Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Pivot/Unpivot Power Query

Hello I have my source in a similar format as the picture below; The format that I want to switch this to is have the months in one column and have 2 seperate columns for 2018 and 2019 sales. ...
Need Help
Riantilo's avatar
Riantilo
Frequent Visitor
7 years ago

Hi,

In my opinion its always better to transform the input table to row level and use visuals to get de desired result. I made a sample .pbix with both the transformation to row level and the desired table. U can download the file here:  https://we.tl/t-PfxLo5Av6T. 


To get to the row level I first Transposed the table. Then I removed 'Sales' from the year column (not necessary). Ater that I Promoted the first row to headers and renamed them. After that I selected the product columns and unpivoted them to get to row level ('Convert to Rows' table in file).  Within the report I made a Matix that shows the desired result.

 

To get the result using Power Query after the row level there is one more stap ('Option 2' table in file). That is to select the Year column and then choose Pivot and select 'sales' as Value Column. By default the Aggregate function is SUM.

Hope this is useful.

Gr,

Jesse