Forum Discussion
Resolver IITranspose & 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.
Resolver IIThank 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
Community ChampionHandling Blanks in the Unpivoted Column
Power Query can indeed leave blanks as they are during unpivoting, which can be a bit tricky if you need to replace them with zeroes.
To handle this, you can use the Replace Values function after unpivoting to automatically fill in blank cells with 0:
- In Power Query, after unpivoting, select the NUM column (or whichever column now contains the unpivoted values).
- Go to Transform > Replace Values.
- In the Replace Values dialog, leave the Value to Find field blank (this will match blank cells) and set the Replace With field to 0.
- Click OK. All blanks in this column will now be replaced with 0.
Alternatively, if you'd like to handle this step during unpivoting itself, you can first use Fill Down (if blank values indicate the same previous value) or Replace Blanks with 0 on the original table before unpivoting.
- BITomS
Solution Supplier
123abc arimoldi I think there may be some confusion here - the blanks are only brought through within the unpivoting if the data type of the original date columns is text. If the data type is a number, any null values do not generate an associated row after unpivoting. I don't think the data type is specified in this thread, so it depends on this.
That said, converting the data type of the columns (if the type is currently number, which is what I assumed) to text is another option and then doing the unpivoting as per what 123abc outlined.
