sheet header data on multiple rows - how to merge the 2 header rows

skmaddur Using Power Query:

First, the data needs to be in a table. I've named the table Table1.

What you need to do is create 1 copy of the data for the header rows (create an index and filter on odd numbers), and 1 copy for data (create an index and filter on even numbers).

Then, once you have 2 tables with the values you need, create indexes for them - this will provide the criteria for the data merge. Merge the 2 tables on the index, and you'll have a list from A1 to J1 with your data!

Query 1 - The header rows

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] = 1 or [Index] = 3)),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Index"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Index", "Attribute"}),
#"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1)
in
#"Added Index1"

Query 2 - The data rows

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Index] = 2 or [Index] = 4)),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Index"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Index", "Attribute"}),
#"Added Index1" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1)
in
#"Added Index1"

Query 3 - The data merge

let
Source = Table.NestedJoin(Headers, {"Index"}, Values, {"Index"}, "Values", JoinKind.Inner),
#"Expanded Values" = Table.ExpandTableColumn(Source, "Values", {"Value", "Index"}, {"Value.1", "Index.1"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Values",{"Index", "Index.1"})
in
#"Removed Columns"