Unpivoting using first 2 rows as header

[Powderhound]

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.

[MarcelBeug]

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"

[Anonymous]

Marcel,

Good solution, thanks!  It seems the current version of PQ is causing the TAB delimiter to be removed during the "promote headers" step.  This then causes the inability for the remainder of the unpivot and naming steps to execute.

[ImkeF]

I've developped a handy function recently that makes this task a bit more dynamic, so you can easily apply it on any number of rows & columns: http://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-powerq...