Combining headers in excel tables using power query

Question

I currently have data in excel which has two headers that I need to combine. I have found a great article online which creates a custom function to dynamically achieve this:

https://www.ehansalytics.com/blog/2021/6/12/working-with-multiple-row-headers-from-excel-in-power-query

I am using the custom function and it works as described. However, I need the combined header names to be there other way round. For example, the top header contains "Main Header". The second header down contains "Secondary Header". Currently the code will return "Main Header_Secondary Header", but I required "Secondary Header_Main Header".

Unfortunately I cannot change these headers around before hand. So I need the custom function to achieve this. I have tried my best at editing the code but it's always erroring. I am new to creating functions in power query so would appreciate any help.

Below is the code I am using which is a direct copy from the referenced website.

(OriginalTable as table, HeaderRows as number, optional Delimiter as text) =>
let
    DelimiterToUse = if Delimiter = null then " " else Delimiter,
    HeaderRowsOnly = Table.FirstN(OriginalTable, HeaderRows),
    /*  Convert the header rows to a list of lists. Each row is a full list
        with the number of items in the list being the original number of columns*/
    ConvertedToRows = Table.ToRows(OriginalTable),
    /* Counter used by List.TransformMany to iterate over the lists (row data) in the list. */
    ListCounter = {0..(HeaderRows - 1)},
    /*  for each list (row of headers) iterate through each one and
        convert everything to text. This can be important for Excel
        data where it is pulled in from an Excel Table and is kept
        as the Any data type. You cannot later combine numerical
        and text data using Text.Combine */
    Transformation = 
        List.TransformMany(
            ListCounter,
            each {ConvertedToRows{_}}, 
            (Counter, EachList) => List.Transform(EachList, Text.From)
        ),
    /*  Convert the list of lists (rows) to a list of lists (each column of headers is now in
        a list - so you'll have however many lists you originally had columns, and each list
        will have the same number of elements as the number of header rows you give it in the
        2nd parameter */
    ZipHeaders = List.Zip(Transformation),
    /*  Combine those lists back to a single value. Now there is just a list of the actual
        column header, combined as one value, using a space, or the chosen delimiter. */
    CombineHeaders = 
        List.Transform(
            ZipHeaders, 
            each Text.Trim(Text.Combine(_, DelimiterToUse))
        ),
    /*  Convert this list back to a single column table. */
    BackToTable = Table.FromList(CombineHeaders, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    /*  Transpose this table back to a single row. */
    TransposeToRow = Table.Transpose(BackToTable),
    /*  Append the original data from the source table to this. */
    NewTable = 
        Table.Combine(
            {
                TransposeToRow, 
                Table.Skip(OriginalTable, HeaderRows)
            }
        ),
    /*  Promote the new first row to the header. */
    PromoteHeaders = Table.PromoteHeaders(NewTable, [PromoteAllScalars=true])
in
    PromoteHeaders

Thanks in advance!

Answer 1

Modify this line:

ListCounter = {0..(HeaderRows - 1)},

to

ListCounter = List.Reverse({0..(HeaderRows - 1)}),