Forum Discussion
sheet header data on multiple rows - how to merge the 2 header rows
| a1 | b1 | c1 | d1 | e1 | f1 | ||||
| 100 | patx | 3/22/2019 | medicine | 3/23/2019 | ins | ||||
| g1 | h1 | j1 | |||||||
| authroized | na | na |
The above is the structure of my excel data export from a 3rd party application. All bold a1-j1 are header columns with data on the 2nd rows. How do I merge these 2 header rows
2 Replies
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"a1 b1 c1 d1 e1 f1 100 patx 3/22/2019 medicine 3/23/2019 ins1 g1 h1 j1 authroized na na 100 paty 4/22/2019 eyecare 4/23/2019 ins2 g1 h1 j1 not auth na na 100 patz 3/25/2019 legcare 3/26/2019 ins3 100 patc 3/26/2019 handcare 3/27/2019 ins4 Thank you for your solution. Even though i don't have the powerquery add-in, i understood your solution from your description. I could filter like you said to separate the odd and even rows of header and data. Upon doing so, i found the above variation in the data. Please note the last 2 lines in the table example above without the 2nd header row (g1,h1,j1).
