Concatenate Table Headers with First Row in Power Query
Concatenate Table Headers with First Row in Power Query
Today I found myself in the situation of dealing with a table like the one below, where the headers needed to be concatenated with the corresponding value found in the first row of the table.
Power Query Concatenate Headers
This scenario can be solved in two different ways. The first one is to transpose the table, demote the headers and the merge column1 and column2, re-transpose the table and promote the headers:
let
Source = Excel.CurrentWorkbook(){[Name=”Weather”]}[Content],TrnspTable = Table.Transpose(Table.DemoteHeaders(Source)),
MergeCol = Table.CombineColumns(TrnspTable,
{“Column1″,”Column2”},
Combiner.CombineTextByDelimiter(” “,QuoteStyle.None),
“NewHeader”),TrnspTable2 = Table.Transpose(MergeCol),
PromHeaders = Table.PromoteHeaders(TrnspTable2)
in
PromHeaders
The second method makes use of the List.Zip function where first I “zipped” the header names with the values in the first row, then I transformed the resulting list of lists by combining the items in each list by a space and then I re-zipped the header names with the resulting list:
let
// get Source Data
Source = Excel.CurrentWorkbook(){[Name=”Weather”]}[Content],// create a list of lists zipping the first row with the column names
ZipRowsRowAndColNames = List.Zip({Table.ColumnNames(Source),
Record.ToList(Source{0})}),// concatenate the items in the list of of lists
AppendItemsInList = List.Transform(ZipRowsRowAndColNames,
each _{0} & ” ” & _{1}),// zip the new list with the column names
NewHeaders = List.Zip({Table.ColumnNames(Source),
AppendItemsInList
}),// rename the columns
TableWithNewHeaders = Table.RenameColumns(Source,NewHeaders),// skip first Row
FinalTable = Table.Skip(TableWithNewHeaders,1)
in
FinalTable
The first method is undoubtedly easier to understand and for not so large tables it is probably worth going for this one but for large tables I would use the second method so to avoid having to transpose a huge table, even though you could select just the first row and then transpose the table and merge column1 and column2 like in the first method and then apply the new headers to the bigger table.