What Is Unpivot in Power BI? — Pivot and Unpivot Explained
Unpivot in Power BI is a Power Query transformation that converts columns into rows — turning a wide, crosstab-style table into a tall, normalized ‘Attribute–Value’ format. It is the reverse of pivoting and is essential for reshaping data so Power BI visuals and DAX measures work correctly.
Why Do You Need to Unpivot Data in Power BI?
Many source files (especially Excel exports) store data in a pivoted, crosstab layout — months as columns, products as rows. While easy to read as a spreadsheet, this format is difficult for Power BI to chart and measure. Unpivoting normalizes the data into a single ‘Month’ column and a single ‘Value’ column, letting slicers, filters, and time-intelligence DAX functions work as expected.
How to Unpivot Columns in Power BI
Select the columns you want to unpivot (e.g., Jan, Feb, Mar, … Dec), then click Transform → Unpivot Columns. Power Query creates two new columns: ‘Attribute’ (containing the old column headers) and ‘Value’ (containing the cell values). Rename them to something meaningful (e.g., ‘Month’ and ‘Sales Amount’).
Unpivot Columns vs. Unpivot Other Columns
Power Query offers three unpivot options:
- Unpivot Columns: Unpivots only the selected columns.
- Unpivot Other Columns: Keeps the selected columns fixed and unpivots everything else. This is the recommended approach because it automatically handles new columns added to the source later (e.g., a new month column).
- Unpivot Only Selected Columns: Same as Unpivot Columns — explicitly unpivots only what you select.
Power BI Unpivot Multiple Columns — Practical Example
Suppose you have a table with columns: Product, Q1_Revenue, Q2_Revenue, Q3_Revenue, Q4_Revenue. Select the Q1–Q4 columns, right-click → Unpivot Columns. The result is three columns: Product, Attribute (Q1_Revenue, Q2_Revenue, etc.), and Value. Rename Attribute to ‘Quarter’ and clean the values (e.g., extract just ‘Q1’) using Replace Values or a Column From Examples step.
How to Unpivot Columns in Power BI — Step-by-Step Tutorial
This tutorial shows you how to transform a wide, crosstab-style Excel table into a normalized tall format using Unpivot in Power Query. You will finish in 4 steps and under 5 minutes.
Steps
Step 1: Load data into Power Query
Open Power BI Desktop, click Home → Get Data, select your file, and click Transform Data to open Power Query Editor.
Step 2: Select columns to keep fixed
Click the columns that should remain as-is (e.g., Product, Region). Hold Ctrl to select multiple.
Step 3: Unpivot the rest
With the fixed columns selected, go to Transform → Unpivot Other Columns. Power Query creates ‘Attribute’ and ‘Value’ columns from all the other columns.
Step 4: Rename and clean
Double-click the ‘Attribute’ column header and rename it (e.g., ‘Month’). Do the same for ‘Value’ (e.g., ‘Sales Amount’). Use Replace Values if the attribute names need cleaning (e.g., removing a prefix). Click Close & Apply.
