Why dataflows centralize data preparation in Power BI - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a dataflow that extracts data from a source.

Power BI

let Source = [1] in Source

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

AExcel.Workbook(File.Contents("data.xlsx"))

BDateTime.LocalNow()

CText.Upper

DTable.AddColumn

Attempts:

3 left

💡 Hint

Common Mistakes

Using transformation functions instead of data source functions.

Trying to use functions that do not load data.

The Excel.Workbook(File.Contents()) function loads data from an Excel file, which is a common first step in dataflows.

2fill in blank

medium

Complete the code to add a column that calculates sales tax in the dataflow.

Power BI

let AddedTax = Table.AddColumn(Source, "Tax", [1]) in AddedTax

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

AText.Length([Sales])

Beach [Sales] * 0.1

C[Sales] + 0.1

DDateTime.LocalNow()

Attempts:

3 left

💡 Hint

Common Mistakes

Adding 0.1 instead of multiplying by 0.1.

Using text functions on numeric columns.

The expression each [Sales] * 0.1 correctly calculates 10% tax on the Sales column.

3fill in blank

hard

Fix the error in the dataflow code that merges two tables.

Power BI

let Merged = Table.NestedJoin(Table1, {"ID"}, [1], {"ID"}, "NewTable") in Merged

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

ATable2

BTable1

CSource

DTable3

Attempts:

3 left

💡 Hint

Common Mistakes

Using the same table twice.

Using an undefined table name.

The Table.NestedJoin function requires the second table to join with. Here, Table2 is the correct table to join with Table1.

4fill in blank

hard

Fill both blanks to filter rows where Sales are greater than 1000 and select only the Sales and Region columns.

Power BI

let Filtered = Table.SelectRows(Source, each [[1]] [2] 1000), Selected = Table.SelectColumns(Filtered, {"Sales", "Region"}) in Selected

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

ASales

B>

C<

DRegion

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column name.

Using '<' instead of '>'.

To filter rows where Sales are greater than 1000, use [Sales] > 1000.

5fill in blank

hard

Fill all three blanks to create a dataflow step that groups data by Region, counts rows, and renames the count column to 'TotalSales'.

Power BI

let Grouped = Table.Group(Source, {"[1]"}, {{"Count", each Table.RowCount([2]), type number}}), Renamed = Table.RenameColumns(Grouped, {{"Count", "[3]"}}) in Renamed

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

ARegion

B_

CTotalSales

DSource

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong column names for grouping.

Not referencing the correct table in Table.RowCount.

Forgetting to rename the column.

The data is grouped by Region. The row count is calculated on each group using Table.RowCount(_). Finally, the count column is renamed to TotalSales.