none
merging rows RRS feed

  • Question

  • I was wondering if it is possible to merge a few rows which contain the header information for the columns.  The excel file I receive regularly distributes the headers over 3 or 5 rows. I would like to concatenate this info into one row before turning that into the column headers.

    Please and thanks

    Thursday, November 10, 2016 10:54 PM

Answers

  • Hi! You can transpose table, merge first columns that now contains headers, then transpose table again. 


    Maxim Zelensky Excel Inside

    Friday, November 11, 2016 10:47 AM
  • A similar solution is to remove all but the header rows, transpose and merge, combine this with the table data and promote headers.

    Somehow you need to determine which rows are header rows.

    Example code for 3 header rows in a named range (note the linefeed character in the #"Merged Columns" step):

    Edit: code adjusted so all columns (based on Table.ColumnNames) will be merged in step #"Manual Merged Columns"

    let
        Source = Excel.CurrentWorkbook(){[Name="NamedRange"]}[Content],
        #"Kept First Rows" = Table.FirstN(Source,3),
        #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
        #"Manual Merged Columns" = Table.CombineColumns(#"Transposed Table",Table.ColumnNames(#"Transposed Table"),Combiner.CombineTextByDelimiter("#(lf)", QuoteStyle.None),"Header"),
        #"Transposed Table1" = Table.Transpose(#"Manual Merged Columns"),
        #"Manual Tables combined" = Table.Combine({#"Transposed Table1",Table.Skip(Source, 3)}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Manual Tables combined")
    in
        #"Promoted Headers"



    Friday, November 11, 2016 11:28 AM

All replies

  • Hi! You can transpose table, merge first columns that now contains headers, then transpose table again. 


    Maxim Zelensky Excel Inside

    Friday, November 11, 2016 10:47 AM
  • A similar solution is to remove all but the header rows, transpose and merge, combine this with the table data and promote headers.

    Somehow you need to determine which rows are header rows.

    Example code for 3 header rows in a named range (note the linefeed character in the #"Merged Columns" step):

    Edit: code adjusted so all columns (based on Table.ColumnNames) will be merged in step #"Manual Merged Columns"

    let
        Source = Excel.CurrentWorkbook(){[Name="NamedRange"]}[Content],
        #"Kept First Rows" = Table.FirstN(Source,3),
        #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
        #"Manual Merged Columns" = Table.CombineColumns(#"Transposed Table",Table.ColumnNames(#"Transposed Table"),Combiner.CombineTextByDelimiter("#(lf)", QuoteStyle.None),"Header"),
        #"Transposed Table1" = Table.Transpose(#"Manual Merged Columns"),
        #"Manual Tables combined" = Table.Combine({#"Transposed Table1",Table.Skip(Source, 3)}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Manual Tables combined")
    in
        #"Promoted Headers"



    Friday, November 11, 2016 11:28 AM