Pivoting and Unpivoting are useful data transformation techniques in Power BI that help reshape data into a structure that is easier to analyze and visualise. Using these features in Power Query, you can convert rows into columns or columns into rows depending on how your data is organised and how you want to work on it.
Understanding Pivoting in Power BI
Pivoting in Power BI is a data transformation technique used to reorganise data by turning category values from rows into separate columns. This results in a wider, more structured table that is easier to analyze and compare.
- Converts unique row-level category values into individual column headers
- Summarizes corresponding data using aggregation methods such as sum, average or count
- Improves data readability and supports side-by-side comparison across categories
- Helps create analysis-ready tables for reports and dashboards
When to Use Pivoting
Pivoting is especially useful in the following scenarios:
- When categorical data is stored vertically but needs to be analyzed side by side
- When building summary tables for high-level reporting
- When preparing data for matrix, table or KPI visuals
- When reducing repetitive rows to create a compact and interpretable dataset
Example
Imagine a table where each row contains:
- Store ID
- Cost Type (Construction, Maintenance, Utilities)
- Cost Amount
By pivoting on Cost Type Power BI converts each cost category into a separate column resulting in a cleaner more structured table where costs can be compared easily across stores.
Understanding Unpivoting in Power BI
Unpivoting is the reverse of pivoting. It converts multiple columns into rows, creating a more normalized dataset. It moves values from multiple related columns into rows, making the dataset more flexible, consistent and easier to analyze.
When to Use Unpivoting
Unpivoting is especially useful in the following situations:
- When working with wide datasets that are difficult to analyze or scale
- When preparing data for charts, slicers and DAX calculations
- When applying uniform calculations or filters across similar categories
Example
Consider a dataset that contains separate columns such as Construction Cost, Maintenance Cost and Utility Cost. While this wide structure may look organized, it limits flexibility when filtering or visualizing data. By unpivoting these cost columns, Power BI transforms the dataset into:
- A single Cost Type column that identifies the category
- A single Cost Amount column that stores the values
The result is a longer but more manageable dataset that works seamlessly with filters, visuals and aggregations, enabling more dynamic and scalable analysis.
How to Pivot and Unpivot Data in Power BI
Pivoting and unpivoting are essential data transformation techniques in Power BI that help reshape datasets for better analysis and reporting. Both operations are performed using the Power Query Editor which allows you to reorganize rows and columns without altering the original data source.
Accessing the Power Query Editor in Power BI
You can download dataset from here
- Load your data into Power BI Desktop
- Go to Home then Transform Data
- The Power Query Editor opens allowing you to reshape your data
Steps to Pivot Data
1. Open the Transform tab in the Power Query Editor.
2. Select the column you want to pivot (for example, Month).
3. Click Pivot Column to convert row values into separate columns.
4. Choose Sales as the Values Column for populating the pivoted data.
5. In Advanced Options, select don’t aggregate for unique values or choose an aggregation like Sum or Average as required.
6. Click Close and Apply to apply the transformation and load the updated data into Power BI.
Steps to Unpivoting Data
1. Load the pivoted dataset into Power BI Desktop.
2. Open the Power Query Editor by clicking Transform Data.
3. Select the columns to unpivot such as January, February and March.
4. Go to the Transform tab and choose Unpivot Columns.
5. Power BI converts the selected columns into two new columns: Month (attribute) and Sales (value).
6. Click Close and Apply to confirm the changes and load the unpivoted data.
Advantages of Pivoting and Unpivoting
- Organizes raw data into a clean, structured format that simplifies analysis and interpretation.
- Makes aggregation, grouping, filtering and advanced DAX calculations more efficient.
- Improves compatibility with Power BI visuals like charts, tables and slicers.
- Enables clearer comparisons across categories and supports interactive dashboards.
- Reduces dataset complexity by creating a normalized, scalable and analytics-friendly data model.