Power Query interface overview in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to load data into Power Query by selecting the correct button.

Power BI

Click the [1] button to import data from an Excel file.

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

AClose & Load

BRefresh

CTransform Data

DGet Data

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing 'Close & Load' which is for loading data after editing.

Selecting 'Transform Data' which opens the editor but does not import data.

Clicking 'Refresh' which updates existing data.

The Get Data button is used to import data into Power Query from various sources like Excel files.

2fill in blank

medium

Complete the code to rename a column in Power Query using the correct menu option.

Power BI

Right-click the column header and select [1] to change the column name.

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

ARename

BRemove

CDuplicate

DSplit Column

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting 'Remove' which deletes the column.

Choosing 'Duplicate' which copies the column.

Picking 'Split Column' which divides the column into parts.

To rename a column, you right-click the header and choose Rename.

3fill in blank

hard

Fix the error in the step name by selecting the correct syntax for referencing the previous step in Power Query.

Power BI

ChangedType = Table.TransformColumnTypes([1], {{"Age", Int64.Type}})

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

AChangedType

BSource

CPreviousStep

Dsource

Attempts:

3 left

💡 Hint

Common Mistakes

Using lowercase 'source' which causes an error.

Referencing a step name that does not exist.

Using the current step name instead of the previous step.

The previous step is usually named Source with a capital S in Power Query. It is case-sensitive.

4fill in blank

hard

Fill both blanks to filter rows where the 'Status' column equals 'Active' in Power Query.

Power BI

FilteredRows = Table.SelectRows(Source, [1][[2]] = "Active")

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

Aeach

BStatus

CSource

DTable

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Source' or 'Table' instead of 'each' in the first blank.

Using wrong column names or misspelling 'Status'.

The each keyword is used to define a function for filtering, and Status is the column name to check.

5fill in blank

hard

Fill all three blanks to create a new column 'AgeGroup' that categorizes ages in Power Query.

Power BI

AddColumn = Table.AddColumn(Source, "AgeGroup", each if [1] < 18 then [2] else [3])

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

A[Age]

B"Minor"

C"Adult"

DAge

Attempts:

3 left

💡 Hint

Common Mistakes

Not using square brackets for the column name.

Missing quotes around text labels.

Using variable names without quotes for text.

The expression uses the age column [Age] to check if less than 18, then assigns the text "Minor", else "Adult".

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