Query folding in Power Query in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a query that folds by referencing the source table.

Power BI

let Source = Sql.Database("ServerName", [1]) in Source

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

A"DatabaseName"

B"TableName"

C"QueryName"

D"ServerName"

Attempts:

3 left

💡 Hint

Common Mistakes

Using the server name twice instead of the database name.

Using the table name instead of the database name.

The Sql.Database function requires the database name as the second argument to connect and enable query folding.

2fill in blank

medium

Complete the code to filter rows where the column "Status" equals "Active" and keep query folding.

Power BI

let FilteredRows = Table.SelectRows(Source, each [Status] [1] "Active") in FilteredRows

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

A!=

B=

C<>

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using inequality operators which filter out the desired rows.

Using comparison operators like < or > which do not apply to text.

The = operator filters rows where the "Status" column exactly matches "Active".

3fill in blank

hard

Fix the error in the code to keep query folding when adding a calculated column doubling the "Sales" value.

Power BI

let AddedColumn = Table.AddColumn(Source, "DoubleSales", each [Sales] [1] 2) in AddedColumn

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

A-

B+

C*

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using addition instead of multiplication.

Using division or subtraction which do not double the value.

To double the sales value, multiply the "Sales" column by 2 using the * operator.

4fill in blank

hard

Fill both blanks to create a query folding step that groups rows by "Category" and sums "Amount".

Power BI

let GroupedRows = Table.Group(Source, [1], {{"TotalAmount", [2], "Amount"}}) in GroupedRows

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

A{"Category"}

BList.Sum

CTable.Sum

DSum

Attempts:

3 left

💡 Hint

Common Mistakes

Using a string instead of a list for grouping columns.

Using a non-existent function like Table.Sum.

Grouping requires a list of columns, so use {"Category"}. To sum values, use the function List.Sum.

5fill in blank

hard

Fill the blanks to create a query folding step that filters rows where "Date" is after 2023-01-01 and selects only "ID" and "Date" columns.

Power BI

let Filtered = Table.SelectRows(Source, each [Date] [1] #date(2023, 1, 1)), Selected = Table.SelectColumns(Filtered, [2]), Result = Selected in Result

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

A>

B{"ID", "Date"}

C{"Date", "ID"}

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using < which filters dates before 2023-01-01.

Using a string instead of a list for columns.

Use > to filter dates after 2023-01-01. The columns to select must be a list, so {"ID", "Date"} is correct.