CSV and text file import in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to load a CSV file in Power BI using Power Query M language.

Power BI

Source = Csv.Document(File.Contents([1]),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None])

Drag options to blanks, or click blank then click option'

ACsv.Load("sales.csv")

BFile.Load("sales.csv")

CLoadFile("C:/data/sales.csv")

D"C:/data/sales.csv"

Attempts:

3 left

💡 Hint

Common Mistakes

Using functions like LoadFile or Csv.Load which do not exist in Power Query M.

Not providing the file path as a string.

In Power Query M, File.Contents requires the full file path as a string to load the CSV file.

2fill in blank

medium

Complete the code to specify the delimiter when importing a text file in Power Query M.

Power BI

Source = Csv.Document(File.Contents("data.txt"), [Delimiter=[1], Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.Csv])

Drag options to blanks, or click blank then click option'

A";"

B":"

C#(tab)

D","

Attempts:

3 left

💡 Hint

Common Mistakes

Using semicolon or other incorrect delimiters for tab-delimited files.

Using comma when the file is tab-delimited.

The tab character is represented as #(tab) in Power Query M to specify tab-delimited files.

3fill in blank

hard

Fix the error in the code to correctly import a CSV file with headers in Power Query M.

Power BI

Source = Csv.Document(File.Contents("sales.csv"), [Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None])
PromotedHeaders = Table.PromoteHeaders([1])

Drag options to blanks, or click blank then click option'

AFile.Contents

BSource

CCsv.Document

DTable.PromoteHeaders

Attempts:

3 left

💡 Hint

Common Mistakes

Passing the function Csv.Document instead of the loaded data variable.

Passing File.Contents which returns binary data.

The Table.PromoteHeaders function requires a table as input, which is the Source variable holding the CSV data.

4fill in blank

hard

Fill both blanks to correctly filter rows where the 'Status' column equals 'Active' after importing a CSV.

Power BI

FilteredRows = Table.SelectRows(PromotedHeaders, [1][Status] [2] "Active")

Drag options to blanks, or click blank then click option'

Aeach

B==

C=

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using double equals == which is not valid in M.

Omitting each keyword.

In Power Query M, Table.SelectRows uses a function starting with each and the equality operator is =.

5fill in blank

hard

Fill all three blanks to convert a CSV import to a table, promote headers, and change the data type of the 'Date' column to date.

Power BI

Source = Csv.Document(File.Contents("data.csv"), [Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None])
TableWithHeaders = [1](Source)
ChangedType = Table.TransformColumnTypes(TableWithHeaders, {{"Date", [2].[3])

Drag options to blanks, or click blank then click option'

ATable.PromoteHeaders

Btype

Cdate

DTable.TransformColumns

Attempts:

3 left

💡 Hint

Common Mistakes

Using Table.TransformColumns instead of Table.TransformColumnTypes.

Using 'Date' as a string instead of a type.

First, promote headers with Table.PromoteHeaders. Then, use Table.TransformColumnTypes with type.date to change the 'Date' column type.

Practice

1. What is the primary purpose of importing CSV or text files into Power BI?

easy

A. To export Power BI reports to CSV format

B. To create visualizations without any data

C. To bring external data into Power BI for analysis

D. To write DAX formulas automatically

Solution

1. Step 1: Understand the role of CSV/text import

   CSV and text file import allows users to bring external data into Power BI.

2. Step 2: Identify the main use of imported data

   Imported data is used for analysis and creating reports in Power BI.

3. Final Answer:

   To bring external data into Power BI for analysis -> Option C

4. Quick Check:

   Importing data = Bringing external data in [OK]

Hint: Import means bringing data inside Power BI [OK]

Common Mistakes:

• Confusing import with export
• Thinking import creates visuals automatically
• Assuming import writes formulas

2. Which of the following is the correct way to start importing a CSV file in Power BI Desktop?

easy

A. Click 'Get Data' > 'CSV' and select the file

B. Write a DAX formula to load the CSV

C. Use the 'New Measure' button to import CSV

D. Drag and drop the CSV file into the report canvas

Solution

1. Step 1: Locate the import option in Power BI Desktop

   The correct method is to use the 'Get Data' button and choose 'CSV' to import files.

2. Step 2: Confirm the import process

   After selecting the CSV file, Power BI loads it into the data model or Power Query Editor.

3. Final Answer:

   Click 'Get Data' > 'CSV' and select the file -> Option A

4. Quick Check:

   Get Data > CSV = Correct import method [OK]

Hint: Use 'Get Data' menu to import files [OK]

Common Mistakes:

• Trying to import CSV via DAX formulas
• Using 'New Measure' which is for calculations
• Dragging files onto report canvas doesn't import

3. After importing a CSV file with columns 'Date', 'Sales', and 'Region', which Power Query step will correctly change the 'Date' column to date type?

medium

A. Table.AddColumn(Source, "Date", each DateTime.LocalNow())

B. Table.RenameColumns(Source, {{"Date", type date}})

C. Table.RemoveColumns(Source, {"Date"})

D. Table.TransformColumnTypes(Source, {{"Date", type date}})

Solution

1. Step 1: Identify the function to change column type

   Table.TransformColumnTypes changes the data type of specified columns.

2. Step 2: Confirm correct syntax for changing 'Date' column to date type

   The syntax Table.TransformColumnTypes(Source, {{"Date", type date}}) correctly changes 'Date' column type.

3. Final Answer:

   Table.TransformColumnTypes(Source, {{"Date", type date}}) -> Option D

4. Quick Check:

   TransformColumnTypes changes data type [OK]

Hint: Use TransformColumnTypes to set column data types [OK]

Common Mistakes:

• Using RenameColumns instead of TransformColumnTypes
• Removing the column instead of changing type
• Adding a new column instead of modifying existing

4. You imported a CSV file but the data preview shows all columns as 'Any' type and some dates are text. What is the best way to fix this in Power Query?

medium

A. Write a DAX measure to convert text to date

B. Manually change column types using 'Transform' > 'Data Type' menu

C. Delete the query and re-import the file

D. Ignore the issue and load data as is

Solution

1. Step 1: Identify how to fix incorrect data types in Power Query

   Power Query allows manual data type changes via the 'Transform' tab.

2. Step 2: Apply manual data type change to fix text dates

   Changing the column type to 'Date' converts text dates properly before loading.

3. Final Answer:

   Manually change column types using 'Transform' > 'Data Type' menu -> Option B

4. Quick Check:

   Manual type change fixes data type issues [OK]

Hint: Use Transform tab to fix column types before loading [OK]

Common Mistakes:

• Deleting query wastes work done
• Using DAX measure instead of fixing source data
• Ignoring data type issues causes errors later

5. You have multiple CSV files with the same structure in a folder. How can you efficiently import and combine them in Power BI?

hard

A. Use 'Get Data' > 'Folder', select the folder, then combine files in Power Query

B. Import each CSV file separately and merge tables manually in report view

C. Write a DAX formula to union all CSV files after import

D. Copy and paste CSV content into Power BI manually for each file

Solution

1. Step 1: Identify the best method to import multiple CSV files

   Power BI's 'Get Data' > 'Folder' option allows importing all files in a folder at once.

2. Step 2: Use Power Query's combine files feature

   After selecting the folder, Power Query can automatically combine files with the same structure.

3. Final Answer:

   Use 'Get Data' > 'Folder', select the folder, then combine files in Power Query -> Option A

4. Quick Check:

   Folder import + combine = efficient multi-file import [OK]

Hint: Use Folder import to combine many CSVs at once [OK]

Common Mistakes:

• Importing files one by one wastes time
• Trying to union files with DAX which is not for import
• Copy-pasting data is error-prone and slow