Pivoting can be a crucial step in Power Query to transform and reorganize your data to facilitate visualization of your data. Data can be pivoted in several ways and depends on the requirements you have for your data.
The two pivoting options in Power Query are as follows:
- Unpivot Columns - pivots data from columns into rows
- Pivot Columns - pivots data from rows into columns
For the example below, we would need to pivot the data so the columns of sales and profits data will give one rows for sales and another for profits for each date and each product so the 'unpivot column' option would need to be used.
First we would need to select which columns we would want to pivot, which would be all the sales and profit columns. We would then navigate to the transform tab and click the 'unpivot columns' option under the 'any column' section.
This would then pivot the data to give a column, combining the sales/profit and dates with another columns with the values.
The Attribute column would then need to be split to separate the date from the 'sales' / 'profits'
If we wanted to do the opposite action i.e. pivoting from rows to columns, we would need to select the column we want to pivot and choose the 'Pivot Columns' option:
This would bring up the Pivot column box. The first sentence should name the column which you want to pivot. For the 'Value Column', you will need to select the column containing the values which will be contained in the values in the newly created columns.
Then select 'advanced options' and, as we will need to have the same values which are currently in the rows to be in the new columns, we do not want any aggregation so select 'Don't Aggregate' in the drop-down menu and click OK.
This should give the layout below with sales or profit for each date as separate columns:
