Power BI: Unpivot columns for clean data

Short answer

With the unpivot function in Power BI you transform wide data with multiple similar columns into a long format with two columns: one for the name and one for the value. You do this in the Power Query Editor by selecting the columns and choosing 'Unpivot Columns'. The resulting format is better suited for analysis and visualisations in Power BI.

In Power BI it is common to receive data in a wide format where multiple columns contain similar information. This makes analysis difficult and disorganised.

In Power BI it is common to receive data in a wide format where multiple columns contain similar information. This makes analysis difficult and disorganised. In this article you will learn how to apply unpivot columns in Power BI to transform your data into a long format, bringing clarity and flexibility to your reports. This is essential for data and BI teams who want to work more efficiently with their datasets.

What you need

A dataset with multiple columns containing similar data (for example monthly sales figures per product in separate columns).
Access to Power BI Desktop to use Power Query for data transformation.

Step 1: Open Power Query and select the columns

Open your Power BI Desktop file and go to the Power Query Editor via ‘Transform Data’. In your table, select the columns you want to unpivot. These are usually the columns with similar data that you want to merge into one column.

Step 2: Perform the unpivot action

With the desired columns selected, right-click and choose Unpivot Columns. Power Query now converts the selected columns into two columns: one containing the original column names and one containing the corresponding values. This makes your dataset more organised and suitable for further analysis.

Step 3: Adjust column names and data types

After unpivoting it is important to give the new columns clear names, for example ‘Month’ for the column names and ‘Value’ for the data. Also check whether the data types are set correctly, for example text for the month and a number for the value. This prevents errors in your reports.

Tips and common mistakes

Tip: Use unpivot only on columns with similar data to avoid making your model unnecessarily complex.
Mistake: Forgetting to adjust data types after unpivoting can lead to errors in visuals.
Tip: Make a backup of your original data before applying large transformations.

Example use case

Suppose you have a sales report with a column for each month: January, February, March, etc. By unpivoting these columns, you create one ‘Month’ column and one ‘Sales value’ column. This makes it easy to analyse trends, apply filters, and create visuals that provide insight per month.

Closing

By unpivoting columns in Power BI you transform your data into a clear, analysis-friendly format. This helps you gain insights faster and create better reports. You can apply these transformations directly in Power BI Desktop or the online environment at app.powerbi.com.

Our tip: Rename the columns immediately after unpivoting to logical names that fit your data, such as ‘Month’ and ‘Sales value’. Also check the data types: the value column is often text after unpivoting, while you need a number for calculations. Fix this in Power Query before loading the data, so you avoid errors in your visuals.

Frequently asked questions

What tips and common mistakes are there?

- Tip: Use unpivot only on columns with similar data to avoid making your model unnecessarily complex. - Mistake: Forgetting to adjust data types after unpivoting can lead to errors in visuals. - Tip: Make a backup of your original data before applying large transformations.

How do I use Unpivot Columns in Power Query?

Open the Power Query Editor in Power BI Desktop and select the columns you want to unpivot. Hold the Ctrl key to select multiple columns. Right-click and choose 'Unpivot Columns'. Power Query creates two new columns: 'Attribute' for the original column names and 'Value' for the corresponding data. Rename these columns to logical names that fit your dataset.

What is the difference between unpivot and transpose in Power BI?

With transpose, all rows are converted to columns and vice versa: the entire table structure rotates. With unpivot, you select specific columns and convert them to rows, while other columns are kept as identifiers. Unpivot is more suitable for datasets with repeating measurement columns, such as monthly sales figures. Use transpose when the full structure of the table needs to be rotated 90 degrees.