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.