Forum Discussion

Powderhound's avatar
Powderhound
New Member
9 years ago

Unpivoting using first 2 rows as header

Hi,

 

I am relatively new to Power Query and would be grateful if anyone can give me some guidance. I have searched the forums but been unable to find an answer to my specific query.

 

I have a table using 2 index columns and 2 index rows, as shown in the attached photo Current Format and would like to transform into the Desired Format shown below. I have been able to perform an unpivot using only 1 header row but am yet to find a way of unpivoting using 2 header rows. Obviously I can concatenate the header rows before transforming but it will make any subsequent manipulation more difficult and time consuming.

 

Any assistance anyone can give is much appreciated.

 

PH.

 

3 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    Anyhow, you need to combine the 2 header rows first and you can split them later. I don't understand the part "it will make any subsequent manipulation more difficult and time consuming.".

     

    In the code below I used a table with headings "Column1", "Column2"  etcetera and some data from your example.

    First the 2 rows are combined (steps through #"Transposed Table1"), these are concatenated with the other rows, than the columns are unpivotted and the joined column is split again.

     

    let
    Source = Table1,
    #"Kept First Rows" = Table.FirstN(Source,2),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("#(tab)", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    Custom1 = Source,
    #"Removed Top Rows" = Table.Skip(Custom1,2),
    Custom2 = #"Transposed Table1" & #"Removed Top Rows",
    #"Promoted Headers" = Table.PromoteHeaders(Custom2, [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"#(tab)", "Column1"}, {"#(tab)_1", "Column2"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Column1", "Column2"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}})
in
    #"Changed Type"