Unpivoting columns in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

DAX to Unpivot Sales Data by Region

You have a table SalesData with columns: Product, North, South, East, West. Each region column contains sales numbers.

Which DAX expression correctly unpivots the regional sales into two columns: Region and Sales?

A UNPIVOT(SalesData, SalesData[North], SalesData[South], SalesData[East], SalesData[West])

B UNION(SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "North", "Sales", SalesData[North]), SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "South", "Sales", SalesData[South]), SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "East", "Sales", SalesData[East]), SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "West", "Sales", SalesData[West]))

C SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", {"North", "South", "East", "West"}, "Sales", {SalesData[North], SalesData[South], SalesData[East], SalesData[West]})

D SUMMARIZE(SalesData, SalesData[Product], "Region", SalesData[North] & SalesData[South] & SalesData[East] & SalesData[West])

💡 Hint

Think about stacking rows for each region using SELECTCOLUMNS and UNION.

Option B uses UNION and SELECTCOLUMNS to create a new table with one row per product-region combination, effectively unpivoting the columns.

Options B, C, and D are invalid DAX syntax or do not perform unpivoting.

❓ visualization

intermediate

Best Visualization After Unpivoting Sales Data

After unpivoting regional sales data into Region and Sales columns, which visualization best shows sales comparison across regions for each product?

A Pie chart showing total sales per product

B Line chart with Region on X-axis and Product as legend

C Stacked bar chart with Product on X-axis, Sales on Y-axis, and Region as legend

D Table showing Product, Region, and Sales columns

💡 Hint

Think about comparing sales by region side by side for each product.

A stacked bar chart allows easy comparison of sales by region within each product category.

Pie charts and line charts are less effective for this multi-category comparison. A table is less visual.

❓ data_modeling

advanced

Data Model Design for Unpivoted Sales Data

You have unpivoted sales data with columns: Product, Region, and Sales. You want to create a data model that supports filtering by region and product category.

Which modeling approach is best?

A Create separate dimension tables for Product and Region, and link them to the unpivoted sales fact table

B Keep all data in a single flat table without relationships

C Create a dimension table only for Product and keep Region as a column in the fact table

D Create a dimension table only for Region and keep Product as a column in the fact table

💡 Hint

Think about how to enable flexible filtering and reduce data duplication.

Option A follows best practices by separating dimensions and facts, enabling efficient filtering and scalability.

Options A, B, and D limit filtering flexibility or cause data redundancy.

🔧 Formula Fix

advanced

Identify the Error in Unpivoting DAX Expression

Consider this DAX expression intended to unpivot sales columns:

UNION(
  SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "North", "Sales", SalesData[North]),
  SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "South", SalesData[South]),
  SELECTCOLUMNS(SalesData, "Product", SalesData[Product], "Region", "East", "Sales", SalesData[East])
)

What is the error in this expression?

A SalesData table is not referenced correctly

B UNION cannot combine more than two tables

C SELECTCOLUMNS cannot be used with string literals

D Missing the "Sales" column name in the second SELECTCOLUMNS call

💡 Hint

Check the column names in each SELECTCOLUMNS call carefully.

The second SELECTCOLUMNS call lacks the "Sales" column name before SalesData[South], causing a syntax error.

UNION can combine multiple tables, and string literals are valid in SELECTCOLUMNS.

🧠 Conceptual

expert

Why Unpivoting Columns Improves BI Analysis

Why is unpivoting columns into rows often recommended before creating reports and dashboards in BI tools?

A It converts wide data into a normalized format, enabling easier aggregation, filtering, and visualization

B It reduces the total number of rows, improving performance

C It automatically creates calculated columns for each original column

D It eliminates the need for dimension tables in the data model

💡 Hint

Think about how BI tools handle data for filtering and grouping.

Unpivoting transforms data from wide to long format, which is easier for BI tools to aggregate and filter by categories.

It usually increases rows, does not create calculated columns automatically, and does not remove the need for dimension tables.

Practice

1. What does unpivoting columns in Power BI do?

easy

A. It merges multiple tables into one table.

B. It filters rows based on column values.

C. It creates new calculated columns based on existing data.

D. It converts multiple columns into rows to simplify analysis.

Solution

1. Step 1: Understand the purpose of unpivoting

   Unpivoting changes the data layout by turning columns into rows, making repeated measures easier to analyze.

2. Step 2: Compare with other operations

   Merging tables combines tables, calculated columns add new data, and filtering removes rows, which are different from unpivoting.

3. Final Answer:

   It converts multiple columns into rows to simplify analysis. -> Option D

4. Quick Check:

   Unpivoting = Columns to rows [OK]

Hint: Unpivoting flips columns into rows for easier data analysis [OK]

Common Mistakes:

• Confusing unpivoting with merging tables
• Thinking unpivoting creates new columns
• Assuming unpivoting filters data

2. Which step correctly describes how to unpivot columns in Power Query Editor?

easy

A. Select the columns to unpivot, right-click, and choose 'Unpivot Columns'.

B. Select the columns, then click 'Merge Queries'.

C. Select the columns, then click 'Add Column' > 'Custom Column'.

D. Select the columns, then click 'Remove Columns'.

Solution

1. Step 1: Identify the unpivot action in Power Query

   Unpivoting is done by selecting columns, right-clicking, and choosing 'Unpivot Columns'.

2. Step 2: Differentiate from other options

   'Merge Queries' combines tables, 'Add Column' creates new columns, and 'Remove Columns' deletes columns, none unpivot data.

3. Final Answer:

   Select the columns to unpivot, right-click, and choose 'Unpivot Columns'. -> Option A

4. Quick Check:

   Unpivot = Right-click > Unpivot Columns [OK]

Hint: Right-click selected columns and pick 'Unpivot Columns' [OK]

Common Mistakes:

• Choosing 'Merge Queries' instead of unpivot
• Trying to add a custom column to unpivot
• Removing columns instead of unpivoting

3. Given this table before unpivoting:

Product | Jan | Feb | Mar
--------|-----|-----|----
A       | 10  | 15  | 20
B       | 5   | 7   | 8

What will be the result after unpivoting the Jan, Feb, Mar columns?

medium

A. Product | Month | Sales
--------|-------|------
Jan | A | 10
Feb | A | 15
Mar | A | 20
Jan | B | 5
Feb | B | 7
Mar | B | 8

B. Product | Jan | Feb | Mar
--------|-----|-----|----
A | 10 | 15 | 20
B | 5 | 7 | 8

C. Product | Attribute | Value
--------|-----------|------
A | Jan | 10
A | Feb | 15
A | Mar | 20
B | Jan | 5
B | Feb | 7
B | Mar | 8

D. Product | Value
--------|------
A | 10
A | 15
A | 20
B | 5
B | 7
B | 8

Solution

1. Step 1: Understand unpivot output structure

   Unpivoting converts columns Jan, Feb, Mar into rows under a new 'Attribute' column, with their values in 'Value'.

2. Step 2: Match the correct table format

   Product | Attribute | Value
   --------|-----------|------
   A       | Jan       | 10
   A       | Feb       | 15
   A       | Mar       | 20
   B       | Jan       | 5
   B       | Feb       | 7
   B       | Mar       | 8

   shows Product repeated with each month as 'Attribute' and corresponding values, which matches unpivot output.

3. Final Answer:

   Table with columns Product, Attribute, Value listing each month as a row. -> Option C

4. Quick Check:

   Unpivot = Columns to Attribute-Value rows [OK]

Hint: Unpivot creates Attribute and Value columns from selected columns [OK]

Common Mistakes:

• Thinking unpivot keeps original columns
• Dropping the attribute column
• Swapping row and column values incorrectly

4. You tried to unpivot columns in Power Query but the 'Unpivot Columns' option is greyed out. What is the most likely cause?

medium

A. You are in the Data view instead of Power Query Editor.

B. You have not selected any columns to unpivot.

C. Your data contains blank rows.

D. Your table has too many columns.

Solution

1. Step 1: Check selection requirement for unpivot

   The 'Unpivot Columns' option requires at least one column selected; otherwise, it is disabled.

2. Step 2: Eliminate other causes

   Blank rows or table size do not disable the option; being outside Power Query Editor means the option is not visible, not greyed out.

3. Final Answer:

   No columns selected to unpivot. -> Option B

4. Quick Check:

   Unpivot disabled if no columns selected [OK]

Hint: Always select columns before unpivoting [OK]

Common Mistakes:

• Ignoring the need to select columns
• Confusing Power Query Editor with Data view
• Assuming blank rows disable unpivot

5. You have sales data with columns: Region, Product, Q1, Q2, Q3, Q4. You want to analyze quarterly sales trends by region and product. Which is the best approach?

hard

A. Unpivot the Q1, Q2, Q3, Q4 columns to create a 'Quarter' and 'Sales' column.

B. Merge the Q1 to Q4 columns into one column using concatenation.

C. Create calculated columns for each quarter's sales growth.

D. Filter the data to show only Q1 and Q2 sales.

Solution

1. Step 1: Identify the need for unpivoting

   Quarterly sales are spread across columns Q1 to Q4; unpivoting these columns will convert them into rows with 'Quarter' and 'Sales' columns, enabling trend analysis.

2. Step 2: Evaluate other options

   Merging columns concatenates data, which is not useful for numeric analysis; calculated columns for growth require unpivoted data first; filtering limits data and does not reshape it.

3. Final Answer:

   Unpivot the Q1, Q2, Q3, Q4 columns to create a 'Quarter' and 'Sales' column. -> Option A

4. Quick Check:

   Unpivot quarterly columns for trend analysis [OK]

Hint: Unpivot repeated measure columns for time series analysis [OK]

Common Mistakes:

• Trying to merge numeric columns as text
• Skipping unpivot before calculating growth
• Filtering instead of reshaping data