Data often comes in different formats. Sometimes values are stored in rows when they should be columns, or in columns when they should be rows. Power Query provides Pivot and Unpivot features to reshape data without changing its meaning.
These transformations make data easier to analyze, visualize, and report in Power BI.
What is a pivot column?
A pivot column transforms unique values from rows into separate columns. It converts row values into column headers while aggregating the corresponding values.
In Simple Terms: Rows → Columns
Example
Before Pivot
| Month | Sales |
|---|---|
| Jan | 1000 |
| Feb | 1500 |
| Mar | 1200 |
After Pivot
| Jan | Feb | Mar |
|---|---|---|
| 1000 | 1500 | 1200 |
Why Use Pivot?
- Converts rows into columns.
- Creates summarized reports.
- Makes dashboards easier to read.
- Useful for cross-tab reports.
Steps to Pivot Columns
- Open Power Query Editor.
- Select the column to pivot.
- Click Transform → Pivot Column.
- Choose the values column.
- Select an aggregation method (Sum, Count, Average, etc.).
- Click OK.
What is an unpivot column?
An unpivot column converts multiple columns into rows. Instead of having separate columns for each category, the values are stacked into a single column.
In Simple Terms: Columns → Rows
Example
Before Unpivot
| Jan | Feb | Mar |
|---|---|---|
| 1000 | 1500 | 1200 |
After Unpivot
| Month | Sales |
|---|---|
| Jan | 1000 |
| Feb | 1500 |
| Mar | 1200 |
Why Use Unpivot?
- Converts wide tables into normalized data.
- Makes datasets suitable for charts.
- Simplifies filtering and grouping.
- Improves compatibility with Power BI visuals.
Steps to Unpivot Columns
- Open Power Query Editor.
- Select the columns to unpivot.
- Click Transform → Unpivot Columns.
- Rename the generated columns if needed.
- Click Close & Apply.
Difference Between Pivot and Unpivot Columns
| Feature | Pivot Column | Unpivot Column |
|---|---|---|
| Purpose | Converts rows into columns | Converts columns into rows |
| Transformation | Rows → Columns | Columns → Rows |
| Output | Wider table | Longer table |
| Data Shape | Horizontal | Vertical |
| Best For | Summary reports | Data normalization |
| Power BI Usage | Reporting | Data preparation |
When to Use Pivot
- Create summary reports.
- Build matrix-style reports.
- Aggregate values.
- Prepare dashboard-ready data.
When to Use Unpivot
- Clean Excel datasets.
- Prepare data for visualizations.
- Convert cross-tab reports.
- Normalize data for analysis.
Best Practices
- Use Pivot only when summary columns are required.
- Use Unpivot before creating charts and dashboards.
- Verify column names after transformation.
- Keep the data structure consistent before loading.
Conclusion
Pivot and unpivot are powerful data transformation features in Power Query that help reshape data for analysis.
Pivot converts rows into columns and is ideal for summarized reports.
Unpivot converts columns into rows and is best for preparing data for analysis and visualization.
Understanding when to use Pivot and Unpivot helps build cleaner datasets and more effective Power BI reports.
