Merging of two ROWs in Power Query

A multiline header can be quite annoying.

Let´s say you want to process this table in Power Query in Excel or Power BI. The header is in first and in second row, so you need to merge both rows:

In Power Query it appears like this:

So how to merge these these rows?

In Power Query, rows are difficult to join, but columns are easy to join. So we transpose the table:

Fill in the blanks in the header:

And merge the rows in the appropriate order:

Let´s transpose it back, thus returning to the original layout, and promote the header.

But beware of one thing. Power Query is limited to 16 384 columns, so you can´t convert a table with more rows to columns. 

Fortunately, you can “disconnect” the header, adjust it, and then reconnect it to the data. See the code (header is gree, data blue, result red):

Záhlaví je zeleně, data modře, výsledek červeně.

• let
     Source = Excel.Workbook(File.Contents(“C:\Users\beran\Desktop\header.xlsx”), null, true),
     original_data = Source{[Item=”Sheet1″,Kind=”Sheet”]}[Data],
     #”Kept First Rows” = Table.FirstN(original_data,2),
     #”Transposed Table” = Table.Transpose(#”Kept First Rows”),
     #”Filled Down” = Table.FillDown(#”Transposed Table”,{“Column1”}),
     #”Merged Columns” = Table.CombineColumns(#”Filled Down”,{“Column1”, “Column2″},Combiner.CombineTextByDelimiter(” “, QuoteStyle.None),”Merged”),
     my_header = Table.Transpose(#”Merged Columns”),
     my_data = Table.Skip(original_data,2),
     final = Table.Combine({my_header,my_data}),
  final_promoted_headers = Table.PromoteHeaders(final, [PromoteAllScalars=true])
  in
     final_promoted_headers