Excel data import 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'

AFile.Contents

BSalesData.xlsx

CExcel.Workbook

D"C:\\Data\\SalesData.xlsx"

Attempts:

3 left

💡 Hint

Common Mistakes

Using just the file name without path

Not using double backslashes in the file path

Omitting quotes around the file path

To import Excel data, you need to specify the full file path as a string inside File.Contents().

2fill in blank

medium

Complete the code to select the first sheet from the Excel workbook.

Power BI

let
    Source = Excel.Workbook(File.Contents("C:\\Data\\SalesData.xlsx"), null, true),
    FirstSheet = Source[1][Data]
in
    FirstSheet

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

A"Sheet1"

B1

C0

D"Data"

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 instead of 0 for the first sheet

Using the sheet name as a string instead of index

Using the wrong field name instead of [Data]

Power Query tables are 0-indexed. Use {0} to access the first sheet's [Data].

3fill in blank

hard

Fix the error in the code to correctly load the Excel sheet named 'Sales'.

Power BI

let
    Source = Excel.Workbook(File.Contents("C:\\Data\\SalesData.xlsx"), null, true),
    SalesSheet = Table.SelectRows(Source, each [1] = "Sales")[Data]{0}
in
    SalesSheet

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

ATable

BName

CData

DSheet

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Sheet' instead of 'Name' as the column

Filtering by 'Data' which is the content, not the sheet name

Using 'Table' which is not a column name

The column that contains sheet names in the Excel.Workbook output is Name. Filtering by Name = "Sales" selects the correct sheet.

4fill in blank

hard

Fill both blanks to filter rows where the 'Region' column equals 'West' and select only the 'Sales' column.

Power BI

let
    Source = Excel.Workbook(File.Contents("C:\\Data\\SalesData.xlsx"), null, true),
    SalesSheet = Table.SelectRows(Source{0}[Data], each [[1]] = [2])
in
    Table.SelectColumns(SalesSheet, {"Sales"})

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

ARegion

B"West"

C"Sales"

DData

Attempts:

3 left

💡 Hint

Common Mistakes

Using column name without brackets

Not quoting the string value 'West'

Using wrong column names

To filter rows where the 'Region' column equals 'West', use [Region] = "West".

5fill in blank

hard

Fill all three blanks to load the Excel file, filter the sheet named '2023Data', and select columns 'Product' and 'Revenue'.

Power BI

let
    Source = Excel.Workbook(File.Contents([1]), null, true),
    FilteredSheet = Table.SelectRows(Source, each [[2]] = [3])[Data]{0},
    Result = Table.SelectColumns(FilteredSheet, {"Product", "Revenue"})
in
    Result

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

A"C:\\Reports\\AnnualReport.xlsx"

BName

C"2023Data"

DData

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes for file path or sheet name

Using wrong column name for filtering

Forgetting to select columns after filtering

Use the full file path as a string for the first blank, filter by the 'Name' column for the sheet name, and specify the sheet name '2023Data' as a string.