Livio/ June 23, 2018/ Excel, Power Query/ 0 comments

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. 

Download the file

Power Query Concatenate Headers

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.

me

Leave a Comment

Your email address will not be published.

*
*