Pivoting columns in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sales data showing product, month, and sales amount in rows.

Cell	Value
A1	Product
B1	Month
C1	Sales
A2	Apples
B2	Jan
C2	100
A3	Apples
B3	Feb
C3	120
A4	Oranges
B4	Jan
C4	80
A5	Oranges
B5	Feb
C5	90

Formula Trace

PivotedTable = SUMMARIZE( SalesData, SalesData[Product], "Jan Sales", CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Jan"), "Feb Sales", CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Feb") )

Step 1: SUMMARIZE(SalesData, SalesData[Product], "Jan Sales", CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Jan"), "Feb Sales", CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Feb"))

Step 2: Calculate Jan Sales for Apples: CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Jan") where Product = Apples

Step 3: Calculate Feb Sales for Apples: CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Feb") where Product = Apples

Step 4: Calculate Jan Sales for Oranges: CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Jan") where Product = Oranges

Step 5: Calculate Feb Sales for Oranges: CALCULATE(SUM(SalesData[Sales]), SalesData[Month] = "Feb") where Product = Oranges

Step 6: Final Pivoted Table

Cell Reference Map

    A       B       C
1 |Product| Month | Sales |
2 |Apples | Jan   | 100   |
3 |Apples | Feb   | 120   |
4 |Oranges| Jan   | 80    |
5 |Oranges| Feb   | 90    |

References: Product (A2:A5), Month (B2:B5), Sales (C2:C5)

The formula uses columns Product, Month, and Sales from the SalesData table.

Result

    A        B         C
1 |Product | Jan Sales | Feb Sales |
2 |Apples  | 100       | 120       |
3 |Oranges | 80        | 90        |

The pivoted table shows each product with separate columns for January and February sales.

Sheet Trace Quiz - 3 Questions

What does the CALCULATE function do in this formula?

A Sorts the sales data alphabetically

B Filters sales by month and sums them

C Duplicates the sales data

D Deletes sales data for other months

Key Result

SUMMARIZE groups rows by a column and uses CALCULATE with filters to pivot data into new columns.

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