Bird
Raised Fist0
Power BIbi_tool~5 mins

Unpivoting columns in Power BI - Cheat Sheet & Quick Revision

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Recall & Review
beginner
What does unpivoting columns mean in Power BI?
Unpivoting columns means turning columns into rows to make data longer and easier to analyze, like changing a wide table into a tall one.
Click to reveal answer
beginner
Why do we unpivot columns in data preparation?
We unpivot columns to organize data in a way that tools like Power BI can easily summarize and visualize it, especially for time series or category data.
Click to reveal answer
beginner
In Power BI, where do you perform unpivoting of columns?
You perform unpivoting in Power Query Editor by selecting columns and choosing 'Unpivot Columns' from the Transform tab.
Click to reveal answer
intermediate
What is the difference between unpivoting and pivoting?
Unpivoting turns columns into rows (wide to long), while pivoting turns rows into columns (long to wide).
Click to reveal answer
intermediate
How does unpivoting help with creating visuals in Power BI?
Unpivoting organizes data so each row is a single data point, making it easier to create charts like line graphs or bar charts that compare categories over time.
Click to reveal answer
What happens when you unpivot columns in Power BI?
AData is duplicated
BColumns become rows
CData is deleted
DRows become columns
Where do you find the 'Unpivot Columns' option in Power BI?
APower Query Editor
BReport view
CData view
DDashboard
Why is unpivoting useful for time series data?
AIt groups all dates into one column
BIt deletes unnecessary dates
CIt converts dates from columns into rows for easier analysis
DIt creates new date columns
Which of these is NOT true about unpivoting?
AIt makes data wider
BIt makes data longer
CIt helps with better visualization
DIt changes columns into rows
After unpivoting, what do the new columns usually represent?
AOnly dates
BOnly numeric values
CSummarized totals
DCategory and value pairs
Explain in your own words what unpivoting columns does and why it is useful in Power BI.
Think about changing a wide table into a tall table.
You got /3 concepts.
    Describe the steps to unpivot columns in Power BI's Power Query Editor.
    Focus on the tool and menu options.
    You got /3 concepts.

      Practice

      (1/5)
      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
      Recommended

      Ready to prove it?

      Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

      10 questions~5 mineasy hard