Power Query interface overview in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Identify the main sections of the Power Query Editor interface

Which of the following is NOT a main section of the Power Query Editor interface?

A Query pane showing all loaded queries

B Pivot Table Field List for creating pivot tables

C Formula Bar displaying the M code for transformations

D Ribbon with data transformation commands

💡 Hint

Think about what Power Query Editor is used for versus Excel features.

The Pivot Table Field List is part of Excel's interface, not Power Query Editor. Power Query Editor has the Query pane, Ribbon, and Formula Bar.

📊 Formula Result

intermediate

Result of a simple Power Query step

In Power Query, if you start with a table of numbers 1 to 5 and apply the step Table.SelectRows(Source, each [Number] > 3), what will be the output?

Power BI

Source = Table.FromList({1,2,3,4,5}, Splitter.SplitByNothing(), {"Number"})
Filtered = Table.SelectRows(Source, each [Number] > 3)

A All rows unchanged

B Rows with Number 1, 2, and 3 only

C Empty table with no rows

D Rows with Number 4 and 5 only

💡 Hint

Think about what the condition [Number] > 3 means.

The step filters rows to keep only those where the Number column is greater than 3, so only 4 and 5 remain.

❓ Function Choice

advanced

Choose the correct Power Query function to add a new column with doubled values

You want to add a new column to a table that doubles the values in the existing column named Amount. Which Power Query function should you use?

A Table.RemoveColumns(Source, {"Amount"})

B Table.SelectRows(Source, each [Amount] * 2)

C Table.AddColumn(Source, "DoubleAmount", each [Amount] * 2)

D Table.RenameColumns(Source, {"Amount", "DoubleAmount"})

💡 Hint

Think about which function adds a new column with a calculation.

Table.AddColumn adds a new column with the specified calculation. The others either filter rows, remove columns, or rename columns.

❓ data_analysis

advanced

Understanding the effect of applied steps order in Power Query

You have a table with a column Score. You apply two steps in this order:

1. Filter rows where Score > 50
2. Add a new column Bonus with value Score * 0.1

What happens if you reverse the order of these steps?

A The <code>Bonus</code> column will be calculated for all rows before filtering

B The final table is the same in both cases

C The filter will remove rows after the <code>Bonus</code> column is added, so fewer rows

D Power Query will give an error because order of steps cannot be changed

💡 Hint

Think about when calculations happen relative to filtering.

Reversing the steps means the Bonus column is added first for all rows, then filtering removes rows. So the Bonus is calculated on all rows before filtering.

🎯 Scenario

expert

Diagnose why a Power Query step causes an error

You have this Power Query step:

AddedColumn = Table.AddColumn(Source, "NewCol", each [Price] / [Quantity])

Sometimes this step causes a DivideByZero error. Which option best explains why?

A Some rows have <code>Quantity</code> equal to zero, causing division by zero error

B The <code>Price</code> column contains text values instead of numbers

C The <code>Source</code> table is empty, so the step fails

D The <code>NewCol</code> column already exists in the table

💡 Hint

Think about what causes division errors in math.

Dividing by zero is not allowed and causes an error. If any Quantity value is zero, the division fails.