Pivoting columns in Power BI - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to pivot the 'Sales' column by 'Region'.

Power BI

PivotTable = Table.Pivot(Source, List.Distinct(Source[Region]), "Region", [1], List.Sum)

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

AQuantity

BDate

CProduct

DSales

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column that is not numeric for pivot values.

Using the column name for rows instead of values.

The pivot operation requires the column to aggregate, which is 'Sales' here.

2fill in blank

medium

Complete the code to aggregate sales by summing after pivoting.

Power BI

PivotTable = Table.Pivot(Source, List.Distinct(Source[Region]), "Region", "Sales", [1])

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

AList.Sum([Sales])

BList.Max([Sales])

CList.Min([Sales])

DList.Average([Sales])

Attempts:

3 left

💡 Hint

Common Mistakes

Using average or min/max instead of sum when total sales are needed.

Summing sales is common to get total sales per region after pivot.

3fill in blank

hard

Fix the error in the pivot code by choosing the correct aggregation function.

Power BI

PivotTable = Table.Pivot(Source, List.Distinct(Source[Region]), "Region", "Sales", [1])

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

AList.Transform([Sales])

BList.Count([Sales])

CList.Sum([Sales])

DList.Sort([Sales])

Attempts:

3 left

💡 Hint

Common Mistakes

Using List.Transform or List.Sort which do not aggregate to a single value.

Using List.Count which counts items but does not sum values.

List.Sum correctly aggregates sales values; others cause errors or wrong results.

4fill in blank

hard

Fill both blanks to pivot sales by 'Product' and aggregate by sum.

Power BI

PivotTable = Table.Pivot(Source, List.Distinct(Source[[1]]), "[1]", [2], List.Sum)

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

AProduct

BRegion

CSales

DDate

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the pivot column and values column.

Using non-numeric columns for aggregation.

Pivoting by 'Product' and aggregating 'Sales' by sum shows total sales per product.

5fill in blank

hard

Fill all three blanks to pivot 'Quantity' by 'Date' and aggregate by sum.

Power BI

PivotTable = Table.Pivot(Source, List.Distinct(Source[[1]]), "[1]", [2], List.[3])

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

ADate

BQuantity

CSum

DSales

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'Sales' instead of 'Quantity' for values.

Using aggregation functions other than 'Sum' for totals.

Pivoting by 'Date' with 'Quantity' values aggregated by List.Sum gives total quantity per date.

Practice

1. What is the main purpose of pivoting columns in Power BI?

easy

A. To create new calculated columns using DAX

B. To turn row values into columns for easier comparison

C. To delete duplicate rows from a table

D. To filter data based on a condition

Solution

1. Step 1: Understand pivoting concept

   Pivoting means converting row values into columns to compare data side by side.

2. Step 2: Identify the purpose in Power BI

   Power BI uses pivoting to reshape data for better visualization and analysis.

3. Final Answer:

   To turn row values into columns for easier comparison -> Option B

4. Quick Check:

   Pivoting = turning rows into columns [OK]

Hint: Pivoting flips rows to columns for side-by-side view [OK]

Common Mistakes:

• Confusing pivoting with filtering
• Thinking pivoting deletes data
• Mixing pivoting with creating calculated columns

2. Which of the following is the correct way to pivot columns in Power Query Editor?

easy

A. Select columns, then choose 'Pivot Columns' from the Transform tab

B. Select columns, then choose 'Unpivot Columns' from the Transform tab

C. Right-click a column and select 'Add Column' to pivot

D. Use the 'Group By' feature to pivot columns

Solution

1. Step 1: Recall Power Query pivot steps

   To pivot columns, you select the column to pivot and use the 'Pivot Columns' option in the Transform tab.

2. Step 2: Eliminate incorrect options

   'Unpivot Columns' does the opposite, 'Add Column' does not pivot, and 'Group By' aggregates but does not pivot.

3. Final Answer:

   Select columns, then choose 'Pivot Columns' from the Transform tab -> Option A

4. Quick Check:

   Pivot Columns button = correct pivot method [OK]

Hint: Use 'Pivot Columns' in Transform tab to pivot [OK]

Common Mistakes:

• Choosing 'Unpivot Columns' instead of 'Pivot Columns'
• Trying to pivot using 'Group By'
• Right-clicking instead of using the ribbon

3. Given this table:

Product | Month | Sales
A       | Jan   | 100
A       | Feb   | 150
B       | Jan   | 200
B       | Feb   | 250

If you pivot the 'Month' column with 'Sales' as values and sum aggregation, what will be the value in the 'Feb' column for Product B?

medium

A. 150

B. 100

C. 350

D. 250

Solution

1. Step 1: Understand pivot operation on given data

   Pivoting 'Month' turns Jan and Feb into columns. Sales values are summed per Product.

2. Step 2: Find Sales for Product B in Feb

   Product B has 250 sales in Feb, so the pivoted 'Feb' column for Product B is 250.

3. Final Answer:

   250 -> Option D

4. Quick Check:

   Sum of Sales for Product B in Feb = 250 [OK]

Hint: Sum values per product after pivoting months [OK]

Common Mistakes:

• Adding sales of Jan and Feb incorrectly
• Mixing product sales values
• Choosing the wrong aggregation

4. You tried to pivot a column in Power Query but got an error saying "Aggregation function required". What is the most likely cause?

medium

A. You selected multiple value columns instead of one

B. You applied pivot on an empty table

C. You did not select an aggregation function for pivoting

D. You tried to pivot a numeric column instead of text

Solution

1. Step 1: Understand pivot error message

   The error "Aggregation function required" means Power Query needs to know how to combine multiple values when pivoting.

2. Step 2: Identify missing step

   You must choose an aggregation like Sum or Count when pivoting if multiple rows map to the same pivoted column.

3. Final Answer:

   You did not select an aggregation function for pivoting -> Option C

4. Quick Check:

   Missing aggregation causes pivot error [OK]

Hint: Always pick aggregation when pivoting multiple values [OK]

Common Mistakes:

• Ignoring the aggregation step
• Assuming pivot works without aggregation
• Confusing numeric and text columns

5. You have sales data with columns: Region, Product, Month, and Sales. You want a table showing each Region as rows and each Month as columns with total Sales. Which steps correctly achieve this pivot in Power Query?

hard

A. Select Month column -> Pivot Columns -> Choose Sales as values -> Select Sum as aggregation

B. Select Region column -> Unpivot Columns -> Choose Sales as values -> Select Average as aggregation

C. Select Product column -> Pivot Columns -> Choose Sales as values -> Select Count as aggregation

D. Select Sales column -> Pivot Columns -> Choose Month as values -> Select Sum as aggregation

Solution

1. Step 1: Identify pivot column and values

   You want Months as columns, so select the Month column to pivot. Sales are the values to aggregate.

2. Step 2: Apply pivot with correct aggregation

   Use 'Pivot Columns' on Month, choose Sales as values, and Sum aggregation to total sales per Region and Month.

3. Final Answer:

   Select Month column -> Pivot Columns -> Choose Sales as values -> Select Sum as aggregation -> Option A

4. Quick Check:

   Pivot Month with Sales sum = correct approach [OK]

Hint: Pivot Month column, aggregate Sales by Sum [OK]

Common Mistakes:

• Pivoting wrong column (Region or Product)
• Using Unpivot instead of Pivot
• Choosing wrong aggregation like Count or Average