Forum Discussion

maracles's avatar
maracles
Resolver II
5 years ago
Solved

Pivoting a column and aggregating text values - Power Query

I'm using Power Query in Excel and I'm trying to transform the data below in the image below. This image is a snipper of a larger dataset and shows information for 2 film titles.    The d...
pivot table
  • DavisBI's avatar
    DavisBI
    5 years ago

    Hi, maracles ,

     

    Suppose we have the following table:

     

    First, you can filter the null value and then transpose it :

        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Fields] <> null)),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows")

     

    Secondly, reference this query twice as "Sheet(2)" and "Sheet(3)" respectively.

     

     

    Then you can append this two query after "remove columns" and "Use first row as Header".

     

     

    Finally, you can get the result you want:

     

     

    Mark this answer as a solution if this helps, thanks!

DavisBI's avatar
DavisBI
Solution Specialist
5 years ago

Hi, maracles ,

 

Suppose we have the following table:

 

First, you can filter the null value and then transpose it :

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Fields] <> null)),
    #"Transposed Table" = Table.Transpose(#"Filtered Rows")

 

Secondly, reference this query twice as "Sheet(2)" and "Sheet(3)" respectively.

 

 

Then you can append this two query after "remove columns" and "Use first row as Header".

 

 

Finally, you can get the result you want:

 

 

Mark this answer as a solution if this helps, thanks!