Data source and dataset in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to load data from an Excel file in Power BI.

Power BI

let Source = Excel.Workbook(File.Contents([1]), null, true) in Source

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

A"data.csv"

B"data.json"

C"data.xlsx"

D"data.txt"

Attempts:

3 left

💡 Hint

Common Mistakes

Using a CSV or JSON file name with Excel.Workbook causes errors.

Forgetting to put quotes around the file name.

Power BI uses Excel.Workbook to load Excel files. The file name must end with .xlsx.

2fill in blank

medium

Complete the code to select the first sheet from the Excel data source.

Power BI

let Source = Excel.Workbook(File.Contents("data.xlsx"), null, true), Sheet1 = Source[1] in Sheet1

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

A{0}

B{1}

C[1]

D[0]

Attempts:

3 left

💡 Hint

Common Mistakes

Using square brackets instead of curly braces for list indexing.

Using index 1 when the first sheet is at index 0.

In Power Query M language, lists use curly braces and are zero-indexed. The first sheet is at index 0 in this context.

3fill in blank

hard

Fix the error in the code to filter rows where the 'Sales' column is greater than 1000.

Power BI

let Source = Excel.Workbook(File.Contents("data.xlsx"), null, true), Sheet1 = Source{1}[Data], FilteredRows = Table.SelectRows(Sheet1, each [1]) in FilteredRows

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

Aeach Sales > 1000

B[Sales] > 1000

C[Sales] => 1000

Deach [Sales] > 1000

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting 'each' before the condition.

Using '=>' instead of '>' for comparison.

Not putting the column name in square brackets.

The Table.SelectRows function requires a function starting with 'each' and the column name in square brackets.

4fill in blank

hard

Fill both blanks to create a new column 'Total' by multiplying 'Quantity' and 'Price'.

Power BI

let Source = Excel.Workbook(File.Contents("data.xlsx"), null, true), Sheet1 = Source{1}[Data], AddedColumn = Table.AddColumn(Sheet1, "Total", each [1] * [2]) in AddedColumn

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

A[Quantity]

B[Price]

CQuantity

DPrice

Attempts:

3 left

💡 Hint

Common Mistakes

Using column names without square brackets.

Using column names as strings instead of references.

In Power Query, column names must be in square brackets when used inside 'each'.

5fill in blank

hard

Fill all three blanks to create a filtered table with only rows where 'Region' equals 'West' and select columns 'Product' and 'Sales'.

Power BI

let Source = Excel.Workbook(File.Contents("data.xlsx"), null, true), Sheet1 = Source{1}[Data], Filtered = Table.SelectRows(Sheet1, each [1] = [2]), Result = Table.SelectColumns(Filtered, [3]) in Result

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

A[Region]

B"West"

C[Product, Sales]

D{"Product", "Sales"}

Attempts:

3 left

💡 Hint

Common Mistakes

Using square brackets with multiple column names as a single string.

Not quoting string values.

Using square brackets instead of curly braces for column list.

Use square brackets for column names, quotes for string values, and curly braces with quoted column names for selecting columns.