Forum Discussion
Transpose & Pivot using Power Query
Hi arimoldi
Excel
I retested your problem by first importing the excel table into the power bi desktop and going to the power query interface to get the following table:
1. If you don't want empty values to display data, consider replacing "null" with Spaces.
In the same step, you just need to change the "0" to " ".
Use the first line as the title.
Delete "Changed Type1" from the step bar on the right.
2. Select the first column and click Fill -> Down. The first column will be filled in automatically.
Finally, the column with the date header is also selected for unpivot.
Change the column name as required.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you all for the answers,
I used the "Unpivot all other columns" functionality as you suggested to create the desired dataset.
I have 2 more questions:
1. if I don't insert a value (eg 0) in the column it won't be unpivoted by Power Query; is there any smart way to manage this? Or do I have to manually insert 0 in all the blank cells?
2. column "ACTIVITY" is associated to another column "MACRO-ACTIVITY" but it is a merged column, so when I use the Unpivot functionality not all the "ACTIVITY" are associated to the related "MACRO-ACTIVITY"; is there any way to do this?
Thanks,
Andrea
arimoldi , it depends if you really need the date-activity combinations with no values? You don't have to enter the zeros manually - there is another option in Power Query to 'replace values', so you could create steps to replace the blank values with a 0 for each column.
If there is another column, you can select both columns in Power Query (CTRL + Select) and then use the 'Unpivot Other Columns' functionality.