Transposing Data in Power BI will convert rows to columns, or columns to rows. It’s best applied to simple datasets. The Power BI pivot function allows users to transpose multiple columns at one time and is far more flexible. We’ll explain how to perform each operation and provide examples of what the data looks like before and after each transformation.
Transposing and pivoting data in Power BI can be one of the more confusing transformations that occur in data prep. We’ll explain some of the use cases and considerations that this technique is useful for.
Let’s jump in!
Table of Contents
Why do You Need to Transpose Data in Power BI?
Transposing data is an essential data preparation step in many cases. It’s significantly easier to work with columns of data in a tabular format in Power BI. Columns in Power Query can be assigned to visuals, added to hierarchies, and automatically formatted as needed. Data that has many individual columns, such as dates assigned to individual columns is incredibly difficult as each column creates a unique field name and would have to be added to a visual one at a time.
Some of the most common use cases for transposing and pivoting data include the following:
- Converting Rows to Columns
- Convert Date Rows to Date Columns
- Dynamically Filter and Data Cleanse Column Headers
Tip: If you are unsure if you should transpose, pivot, or unpivot data, try all three. Create a reference table for each transformation or test the transformation and delete the Power Query step if it does not meet your need.
What Does Transposing Data Do?
When you transpose data in Power BI it will convert rows into columns. In the example below, we applied a transpose function to a simple two row dataset. The data is flipped to move the name of each month in a row, to an individual column name.
You’ll also notice that if you transpose a table two times in a row, you will get back to your starting data. This is a very useful technique when you need to apply specific data preparation steps such as adjusting column names.
Transposing dating will convert columns to rows, or rows to columns depending on the format of your starting data.
How to Transpose Data in Power BI
To transpose data in Power BI, launch the Power Query Editor. Go to the Transform section of the Power Query Ribbon, and Select Transpose. A new applied step will be added to Power Query and the data will be converted from rows to columns, or from columns to rows.
When converting from rows to columns, Power BI will not automatically promote the first row to columns headers.
To promote the first row of data to headers, navigate to Transform, Use First Row as Headers. T
his step will move the first row of data in a dataset to the column names. Column names in Power Query become field names in the Power BI report editor and become the reference points for building DAX formulas.
When transposing or pivoting data into new columns, you may need to adjust the Power BI data types for each column.
To automatically assign data types to new columns, navigate to Transform, and select Detect Data Type. This function will sample the data in each column and assign a data type to it. Data types can be manually overridden by right clicking on a column and selecting the new data type.
After transposing data, promoting the first row to headers, and adjusting data types, you can click on Close and Apply to work with the new data model to build DAX formulas and drag and drop the new columns into visuals for a report or dashboard.
When to Use Pivot vs Transpose in Power BI
Power BI has two primary functions for rotating data. Transpose and Pivot. Transposing flips your data by converting rows to columns or columns to rows. Pivoting will aggregate data from a specific column and present it in a tabular format, often with categories assigned to rows and columns. In many ways it is used to re-create the look of a pivot table with categories assigned to rows and columns, with values filling in the middle of the grid.
The following example shows data that starts with multiple columns and repeating values. It is transformed in Power Query with a pivot action applied to it.
One of the starting columns becomes unique row values, another column becomes unique column headers. The values that fill in the grid are valued aggregated by a sum to show the total of each categorical cross section of data.
To further highlight the difference between Transpose and Pivot, the following table explains when to use each one and common use cases.
| Transpose | Pivot | |
|---|---|---|
| When to Use | When you need to rotate data | When you need to summarize or restructure data for analysis |
| What It Does | Turns rows into columns and columns into rows | Takes unique values from a column and makes them new columns |
| Typical Use Case | Fixing data that has variables as rows | Aggregating data and analyzing it from different angles |
How to Pivot Data in Power BI
To pivot data in Power BI, launch the Power Query Editor, go to the Transform section on the Power Query ribbon, select Pivot Column. Power BI will launch the Pivot Column options screen. Assign a value column, and aggregation method under advanced options.
The Values Column will be the name of the column that contains values for the new table. These are typically the numbers that will be added together and summarized.
The aggregate value function will default to Sum but can also be set to different calculations such as, median, min, max, and more.
After pressing OK, Power Query will pivot the data. Row and column names will be assigned and values will be aggregated based on the selected function.
In the example below, months become rows, categories are columns and values are added at each cross-section of category and month.
To help visualize the pivot function, Microsoft publishes an illustrated guide for the concept. Try experimenting with pivot. If your data looks like the above image, but needs to be in a columnar format Power BI offers another capability related to pivot called unpivot.
How to Unpivot Data in Power BI
To unpivot data in Power BI, launch the Power Query Editor, go to the Transform section on the Power Query ribbon, select Unpivot Column.
Unpivot performs the inverse operation of Pivot in Power BI. It’s often used to convert datasets that have many different columns into a single column. Unpivot is widely used when importing datasets that present months or dates as column headers. It’s often necessary to convert columns to rows so a single field can be assigned to a visual or to enable time-intelligence functions in Power BI.
The example below shows a dataset with months as column names. The unpivot function converts columns with month names to a single column.
Power BI users can use transpose to convert data from columns to rows and rows to columns. While related, Pivot will aggregate data using an assigned function. Unpivot is needed to convert data with multiple columns or rows of information into an easier to work with dataset.
You can multi-select columns prior to performing an unpivot to maintain specific columns.
To multi-select columns hold down SHIFT while left clicking on columns. These columns will remain in a columnar format when applying the unpivot step. Try moving all columns that you want to maintain the hierarchy of to the left of a dataset prior to applying the unpivot step.
Tips and Tricks for Transposing and Pivoting Data
The following tips and tricks will help you determine when to Transpose, Pivot or Unpivot data across some common use cases.
- Transpose Using an Index Column – Add an index column to transpose or pivot on when there is no unique identifier in the data or you need to maintain row integrity. This is a common step when cleansing column headers or performing intermediate data prep steps where the data will ultimately be transformed into its starting layout.
- Transposing Large Datasets – Large datasets can take a long time to transpose. The amount of compute power required is much higher than other types of transformations. You may want to limit the number of rows with a filter prior to applying the transformation step to determine if it is going to meet your needs prior to committing to a long compute time.
- Be aware of nulls – When performing an unpivot it’s possible that null rows existing in your dataset that can cause subsequent errors that were not a problem previously. Turn on Column Distribution and Column Quality under the View section of the Power Query ribbon to check for data cleanliness errors before and after a transformation.
- Use Reference Tables – If you’re unsure of which transformation to apply, you can right click on incoming data and create a reference table. The reference table links to a starting table and lets you apply transformations to it without impacting the initial Power Query steps. It’s a fast way to trial and error data transformation steps.
These tips should help you when manipulating and rotating datasets. The most important tip that we have is to embrace trial and error. It’s common that you will only know which transformation you need after trying them out and seeing if the data is how you want it.
As Patrick from Guy in a Cube says, Pivot or Unpivot, there is no Try! The following video further explains these vital Power BI concepts.
As you can see it’s vital to embrace some trial and error to learn pivot and unpivot. While one is for aggregating and summarizing values like a pivot table, the other is widely used to convert columns into rows for further data prep.
Conclusion
Our article today focused heavily on transforming data with transpose and pivot as a data preparation step within Power Query. It’s an essential skill for getting data into a format that can more easily be worked with.
After data is prepped in Power Query, it’s loaded into the Power BI report and dashboard builder. Data can further be manipulated at the matrix, table, or chart level.
How data is prepped will have a significant impact on how you can work with data within visualizations. As a rule of them, we would rather have dates in a single column than across many columns. It makes it easier to build hierarchies, groupings, and can be essential for assigning a date table relationship for time-intelligence calculations.