Pivoting columns in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate total sales per product category using pivoted columns

You have a table with sales data pivoted by product categories as columns: Electronics, Clothing, and Furniture. Each column shows sales amount for that category per month.

Which DAX measure correctly calculates the total sales across all categories for each month?

Power BI

SalesData = DATATABLE(
    "Month", STRING,
    "Electronics", INTEGER,
    "Clothing", INTEGER,
    "Furniture", INTEGER,
    {
        {"Jan", 1000, 500, 300},
        {"Feb", 1200, 600, 400}
    }
)

// Choose the correct measure:

A Total Sales = CALCULATE(SUM(SalesData[Electronics] + SalesData[Clothing] + SalesData[Furniture]))

B Total Sales = SUMX(SalesData, SalesData[Electronics] * SalesData[Clothing] * SalesData[Furniture])

C Total Sales = SUM(SalesData[Electronics] + SalesData[Clothing] + SalesData[Furniture])

D Total Sales = SUM(SalesData[Electronics]) + SUM(SalesData[Clothing]) + SUM(SalesData[Furniture])

💡 Hint

Think about how to add values from multiple columns correctly.

Option D sums each category column separately and adds them, which correctly totals sales.

Option D multiplies values, which is incorrect.

Option D tries to sum an expression inside SUM, which is invalid syntax.

Option D sums the addition inside SUM, which causes a type error.

❓ visualization

intermediate

Best visualization for pivoted monthly sales data

You have monthly sales data pivoted by product categories as columns. You want to show trends over time for each category clearly.

Which visualization type is best suited for this pivoted data?

A Table showing raw pivoted sales data

B Stacked column chart showing months on X-axis and sales on Y-axis with categories stacked

C Line chart with months on X-axis and separate lines for each product category

D Pie chart showing total sales per category for all months combined

💡 Hint

Think about how to show trends over time for multiple categories.

Line charts are best for showing trends over time with multiple categories as separate lines.

Stacked columns can show totals but make it harder to compare categories individually over time.

Pie charts do not show trends over time.

Tables show raw data but are less visual and harder to interpret trends.

❓ data_modeling

advanced

Handling pivoted columns in data model for flexible analysis

You receive sales data with product categories as separate columns (pivoted). You want to create a data model that allows easy filtering and aggregation by category.

What is the best approach to handle this pivoted data in your model?

A Unpivot the category columns into two columns: Category and Sales, then relate to a Category table

B Keep the pivoted columns as is and create measures summing each column separately

C Create calculated columns that concatenate all category sales into one string per row

D Remove all category columns and use only total sales column

💡 Hint

Think about how to make filtering by category easier and more scalable.

Unpivoting converts columns into rows, creating a normalized structure that supports filtering and aggregation by category.

Keeping pivoted columns limits flexibility and requires many measures.

Concatenating sales into strings prevents numeric analysis.

Removing category columns loses detail.

🔧 Formula Fix

advanced

Identify error in DAX measure summing pivoted columns

Given a table with pivoted sales columns: Electronics, Clothing, Furniture, a developer wrote this DAX measure:

Total Sales = SUM(SalesData[Electronics] + SalesData[Clothing] + SalesData[Furniture])

What error will this measure cause when used in a report?

A SyntaxError due to invalid use of SUM with addition inside

B TypeError because SUM expects a single column but receives an expression

C Returns incorrect total because it sums only Electronics column

D No error, returns correct total sales

💡 Hint

Consider what the SUM function expects as input.

SUM expects a single column reference. Adding columns inside SUM creates an expression, causing a type error.

It is not a syntax error but a semantic error at runtime.

Option B is incorrect because it does not sum only one column.

Option B is incorrect because the measure fails.

🧠 Conceptual

expert

Why unpivoting columns improves BI report performance and flexibility

In a BI project, you receive data with many product categories as separate columns (pivoted). You decide to unpivot these columns into rows before loading into the model.

What are the main benefits of unpivoting pivoted columns in this context?

A Unpivoting creates a normalized structure that simplifies filtering, aggregation, and reduces the number of measures needed

B Unpivoting reduces data size by removing duplicate values and improves compression

C Unpivoting automatically creates calculated columns for each category, improving calculation speed

D Unpivoting merges all category sales into a single column, which improves visualization options

💡 Hint

Think about how data structure affects filtering and measure creation.

Unpivoting creates a normalized table with category and value columns, making filtering and aggregation easier and reducing the need for many separate measures.

It does not necessarily reduce data size or create calculated columns automatically.

Merging sales into a single column without category info reduces analysis options.

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