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.

Practice

1. What is the main purpose of the Applied Steps pane in Power Query interface?

easy

A. To display the final output of the query only

B. To track every change made to the data so you can review or undo them

C. To connect to external data sources

D. To write custom formulas manually

Solution

1. Step 1: Understand the role of Applied Steps pane

   The Applied Steps pane shows each transformation step applied to the data in order.

2. Step 2: Identify its purpose in data editing

   This pane allows users to review or undo any changes made during data shaping.

3. Final Answer:

   To track every change made to the data so you can review or undo them -> Option B

4. Quick Check:

   Applied Steps pane = Tracks changes [OK]

Hint: Applied Steps = history of changes to undo or review [OK]

Common Mistakes:

• Confusing Applied Steps with final output display
• Thinking it connects to data sources
• Assuming it is for writing formulas

2. Which part of the Power Query interface allows you to manage multiple queries easily?

easy

A. Queries pane

B. Applied Steps pane

C. Formula bar

D. Data preview window

Solution

1. Step 1: Identify the pane for managing queries

   The Queries pane lists all queries in the current Power Query session.

2. Step 2: Understand its function

   This pane helps users select, rename, or delete queries easily.

3. Final Answer:

   Queries pane -> Option A

4. Quick Check:

   Queries pane = Manage multiple queries [OK]

Hint: Queries pane lists and manages all queries [OK]

Common Mistakes:

• Confusing Queries pane with Applied Steps pane
• Thinking Formula bar manages queries
• Assuming Data preview window manages queries

3. In Power Query, if you apply a filter step to remove rows with blank values, where will this step appear?

medium

A. In the Applied Steps pane as a new step

B. In the Queries pane as a new query

C. In the Data preview window only

D. In the Ribbon under Home tab

Solution

1. Step 1: Understand where transformation steps are recorded

   Every change like filtering rows is recorded as a step in the Applied Steps pane.

2. Step 2: Identify the correct location for the filter step

   The filter step appears as a new entry in the Applied Steps pane, showing the action taken.

3. Final Answer:

   In the Applied Steps pane as a new step -> Option A

4. Quick Check:

   Filter step = Applied Steps pane entry [OK]

Hint: Filter changes show up in Applied Steps pane [OK]

Common Mistakes:

• Thinking filter creates a new query
• Looking for filter step in Ribbon or Data preview only
• Confusing Queries pane with Applied Steps pane

4. You tried to rename a query in the Queries pane but the name did not change. What is the most likely reason?

medium

A. The Ribbon is disabled

B. The Applied Steps pane is locked

C. You forgot to press Enter or click outside the name box after typing

D. You need to refresh the Data preview window first

Solution

1. Step 1: Understand how renaming works in Queries pane

   After typing a new name, you must press Enter or click outside to confirm the change.

2. Step 2: Identify why the name did not update

   If you do not confirm the edit, the old name remains and the change is lost.

3. Final Answer:

   You forgot to press Enter or click outside the name box after typing -> Option C

4. Quick Check:

   Rename requires confirmation by Enter or click [OK]

Hint: Press Enter after renaming query to save change [OK]

Common Mistakes:

• Assuming Applied Steps pane controls query names
• Thinking Ribbon or Data preview affect renaming
• Not confirming edits after typing

5. You want to combine data from two queries and then filter the combined data. Which sequence of actions in Power Query interface is correct?

hard

A. Rename queries first, then use Data preview window to filter combined data

B. Filter each query separately, then use Queries pane to merge them

C. Use Applied Steps pane to combine queries, then Ribbon to filter data

D. Use the Ribbon's 'Append Queries' to combine, then apply filter step in Applied Steps pane

Solution

1. Step 1: Combine queries using Ribbon's Append Queries

   The Ribbon provides the 'Append Queries' option to combine two queries into one.

2. Step 2: Apply filter step after combining

   After combining, apply the filter step which appears in the Applied Steps pane to shape the data.

3. Final Answer:

   Use the Ribbon's 'Append Queries' to combine, then apply filter step in Applied Steps pane -> Option D

4. Quick Check:

   Combine with Ribbon, filter in Applied Steps [OK]

Hint: Combine first with Ribbon, then filter in Applied Steps [OK]

Common Mistakes:

• Filtering before combining data
• Using Queries pane to merge instead of Ribbon
• Trying to combine in Applied Steps pane