Forum Discussion

arimoldi's avatar
arimoldi
Icon for Resolver II rankResolver II
1 year ago
Solved

Transpose & Pivot using Power Query

Hi,   I have an input dataset in this format:   ACTIVITY / DATE 01/10/2024 02/10/2024 03/10/2024 04/10/2024 05/10/2024 A 8   4 1   B   3   1 5 C   3   1   D   ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

     

     

     

     

arimoldi's avatar
arimoldi
Icon for Resolver II rankResolver II
1 year ago

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

 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.