Unpivoting a dataset involves converting column names into rows and their values into another column. In Power BI, you can unpivot columns to rows using the “Unpivot Columns” option in the “Transform” tab of the “Home” ribbon.
In this article, I will take you through a step-by-step approach on how to unpivot columns to rows in Power BI Desktop.
Let’s get started.
Introduction
To get started, the first thing to do is to insert the data in Power BI. Then Transform Data, which takes you to Power Query Editor.
NB: If you have multiple queries, you must select the query that contains the columns you want to transpose.
After this, select the row you want on the first column (I selected Year), then go to the Transform tab and click on the drop-down on Unpivot Columns. Then select Unpivot Other Columns.
As displayed below, this process has converted the columns to rows.
After this process, the next thing to do is to rename the columns (by double-clicking) and filter out unnecessary values.
Now, you can close and apply your data (to save your setting) in the home tab.
Also, you can do this by using the DAX. You can use the UNPIVOT function. This function takes a table and returns a new table with the columns transposed to rows.
Here is an example of how the DAX formula would look like:
UNPIVOT(my_table, "column_name", "row_name", "value")
You can also pivot the data by using the function PIVOT.
PIVOT(my_table, "column_name", "row_name", "value")
Conclusion: How to Unpivot Columns to Rows
NB: To duplicate the table to Unpivot data and keep the original if you want to keep the original data and create a new table.
Finally, keep in mind that you can only use the Pivot and Unpivot functions on a single column.
If you need to pivot/unpivot multiple columns using the functions, you need to use the Advanced Editor.
I hope you understand these steps.
Thanks for reading.