Pivoting columns in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report that shows total sales by product category for each month in a clear table format.

📊 Data: You have a sales dataset with columns: Date, Product Category, and Sales Amount. The data lists each sale as a separate row with the date and category.

🎯 Deliverable: Create a pivot table that shows months as columns, product categories as rows, and total sales as values.

Sample Data

Date	Product Category	Sales Amount
2024-01-05	Electronics	200
2024-01-15	Clothing	150
2024-02-10	Electronics	300
2024-02-20	Clothing	100
2024-03-05	Electronics	250
2024-03-15	Clothing	200
2024-01-25	Furniture	400
2024-02-28	Furniture	350
2024-03-20	Furniture	300

1

Step 1: Add a new column to extract the month name from the Date column.

Month = FORMAT('Sales'[Date], "MMM")

Expected Result

A new column 'Month' with values like 'Jan', 'Feb', 'Mar' for each row.

2

Step 2: Create a pivot table visual in Power BI.

Add 'Product Category' to Rows, 'Month' to Columns, and 'Sales Amount' to Values with aggregation SUM.

Expected Result

Pivot table showing product categories as rows, months as columns, and total sales summed in the cells.

3

Step 3: Format the pivot table for clarity.

Sort months in calendar order, format sales values as currency, and add a grand total row and column.

Expected Result

A clear, easy-to-read table with months in order Jan, Feb, Mar, sales formatted as currency, and totals shown.

Final Result

          | Jan    | Feb    | Mar    | Total   
------------------------------------------------
Electronics | $200   | $300   | $250   | $750    
Clothing    | $150   | $100   | $200   | $450    
Furniture   | $400   | $350   | $300   | $1,050  
------------------------------------------------
Total       | $750   | $750   | $750   | $2,250  

✓Electronics sales steadily increased from January to March.

✓Furniture had the highest total sales across the three months.

✓Clothing sales were lower but consistent.

Bonus Challenge

Create a line chart showing sales trends over the months for each product category.

Show Hint

Use the 'Month' column on the X-axis, 'Sales Amount' on the Y-axis, and 'Product Category' as the legend.

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