Pivoting a column and aggregating text values - Power Query

[maracles]

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 data I receive is laid out incorrectly, I want to pivot the 'Fields' column so that it becomes my header row, which I can do using 'Pivot Column', my problem is that I want the 'Value' information to then be listed beneath this. 

For example, my first column would become Title, and the first two row values for this column should be The Rig and Good Luck To You, Leo Grande.

I do not know though how to get the transform to aggregate the data like this and list the values as individual rows under the pivoted column headers.

Advice would be much apprecaited. 

[DavisBI · Accepted solution]

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!