如何修改Power Query自定义函数调换Excel合并表头顺序

我在Excel中有两行需要合并的表头，使用自定义函数实现合并后，生成的表头格式为Main Header_Secondary Header，但实际需要的是Secondary Header_Main Header。由于无法提前调换这两行表头，需要修改该自定义函数来实现顺序调换的需求。

修改后的完整代码 #

(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 = List.Reverse({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

修改说明 #

仅需修改ListCounter的定义：将原代码中的ListCounter = {0..(HeaderRows - 1)}替换为ListCounter = List.Reverse({0..(HeaderRows - 1)})。原列表是按从第一行到最后一行表头的顺序遍历，反转后会从最后一行（第二行）表头开始遍历，再到第一行，最终合并后的表头顺序就会变为Secondary Header_Main Header。

内容的提问来源于stack exchange，提问作者Jack Pennington