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
FreemanZSuper User
hi arimoldi ,
1. you can insert a 0 and replace the 0 with "" afterwards
2. try select both macro activity and activity columns, and unpivot other columns.
- arimoldi
Hi,
thanks for the reply.
For point #1 ok, thanks.
For point #2 I tried, but since MACRO_ACTIVITY is composed by merged cells when I unpivot the tablemost of the MACRO_ACTIVITY remain null.
Thanks,
Andrea
- BITomS
Solution Supplier
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.
- 123abc
Community Champion
Handling 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
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.
Hi arimoldi
First of all, thank you very much for your prompt reply!
It seems that you already know the solution to this problem. In response to your two questions:
1. You can use "Replace Values" to replace empty values.
2. Assuming that your two tables have the same ACTIVITY column, merge the two tables based on the ACTIVITY column in the power query. And expand.
You can get a table like this.
After replacing the null value, select all date titled columns to try unpivot.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- arimoldi
Hi,
thanks for the replay.
For point #1 ok, I was wondering if there was another way to manage null instead of replacing them with 0, but anyway it is fine.
For point #2 your solution seems to be applicable only in the case I have a domain table where alla ACTIVITY and MACRO_ACTIVITY are listed, but this is not the case... I have just one table and the MACRO_ACTIVITY are merged cells so when I import the dataset not all the ACTIVITY are associated to the related MACRO_ACTIVITY.
Thanks,
Andrea
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.
