Introduction
Pivoting and unpivoting are essential data transformation techniques used to reshape data for analysis and visualization. These processes allow users to convert data formats, making it easier to derive insights from datasets.
What is Pivoting?
Pivoting is the process of transforming rows into columns. This is particularly useful when you want to aggregate and summarize data based on selected columns.
(Pivoting in the above view, rotates the dataset from the state of rows to columns – creating a new table orientation as shown on the right side)
How to pivot data in Power BI Desktop?
You can pivot data in Power BI Desktop as follows.
Import your data into Power BI.
Open the Power Query Editor.
Highlight the columns you want to pivot – in this case, Brands and Revenue.
Navigate to Transform tab and select Pivot Column.
Configure the pivot.
In the dialog box, select the value column - in this case Revenue.
Do not aggregate the values – so to return exact revenue values.
Executing this - pivots the data (rotates the data from the state of rows into columns or crosstab format) as shown below.
What is unpivoting?
Unpivoting is the reverse process of pivoting; it transforms columns into rows. This technique is useful for normalizing data, making it easier to analyze trends over time or across categories.
How to unpivot data in Power BI Desktop?
Using the unpivot option, I can reverse the resulting data in the previous case of pivoting data into its original state.
To do so.
Open the Power Query Editor
Highlight the columns you wish to unpivot (in this case, I have highlighted all columns containing revenue by brand).
Navigate to the Transform tab and select Unpivot Columns
(Notice, all columns are highlighted – except the column “Quarter”)
Executing these reverses the data from the state of columns (crosstab format) to the state of rows (columnar format) as shown below.
(Note, in the above resulting view, the column “Attribute” has been renamed “Brands” while the column “Value” has been renamed "Revenue”)
Conclusion
Mastering pivot and unpivot operations in Power BI enhances your ability to manipulate and analyze data effectively. These transformations are not only fundamental for preparing datasets but also vital for ensuring that your reports are both informative and visually appealing. Understanding when and how to apply these techniques will significantly improve your data analysis capabilities in Power BI.
If you like the work we do and would like to work with us, drop us an email on our contacts page and we’ll reach out!
Thank you for reading!
