Forum Discussion

Joerobert's avatar
Joerobert
Advocate V
7 years ago

Power Query / Complex Data Transformation / Multiple Headers

Hello BI Community,   I am working with a data source that is excel based and I am very limited in the changes I can make with the structure. See below photos as a reference, photo 1 shows the scru...
Need Help
ImkeF's avatar
ImkeF
Community Champion
7 years ago

For Option1: First you have to unpivot on the first column, adressing the first 2 rows as headers and then pivot back just with the values from the 2nd row.

 

Therefore you have to use my special function here: https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-powerquery-in-excel/ 

 

Use it like this:

 

 

let
    Source = fnUnpivotByNumbers(Table1, 1, 2),
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Attrib.2]), "Attrib.2", "Value")
in
    #"Pivoted Column"