Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database. It allows its users to transform columns into rows and rows into columns. It allows grouping by any data field.
Unpivot is inverse of Pivot it is used to transform rows to columns
Example-
Get Data from a source
Here, I am going to use a table of products which columns as Prod_name, Address and Amount
Click on Edit queries
Then Power Query Editor gets open goto the transform tab
Select a column on which you want to apply Pivot I have selected Address column and click on Pivot column
A Pivot column dialog box will appear it will ask you to select the values column and In advance options, select Don’t Aggregate
Click Ok
The following is the output of a pivot here address rows are transformed to a column and the amount data is distributed in them according to the Prod_name
To Unpivot
Select the column which you want to Unpivot I have selected Mumbai and Pune column for unpivot
Click on Unpivot dropdown and Select Unpivot Only Selected Columns
The data in the table will be arranged as it was before we applied the Pivot function
The columns names are not retained but columns are divided o the basis of Attribute and Value
If you are a newbie to Power BI starts with the following must-watch video:-
