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:

Merging of two ROWs in Power Query

In Power Query it appears like this:

Merging of two ROWs in Power Query

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:

Merging of two ROWs in Power Query

Fill in the blanks in the header:

Merging of two ROWs in Power Query

And merge the rows in the appropriate order:

Merging of two ROWs in Power Query

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

Merging of two ROWs in Power Query

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