如何对Power Query中多列Text.Combine字段进行数值排序？

我生成报表的流程是转置并合并两个独立列（Unpivot、Remove Duplicates、Pivot、Text.Combine、List.Sort）。之前找到过单列排序的方法，但没法实现两列的数值排序，想知道这是否可行？

原始报表：

期望结果：

我当前的代码如下，尝试添加"Division #"列的排序逻辑时，会导致"Warehouse Number"列的排序失效：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Case GTIN Number"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attribute]), "Attribute", "Value",each Text.Combine(_, ",")),
    #"Sort Column" = Table.TransformColumns(#"Pivoted Column",
    {"Warehouse Number", each Text.Combine(List.Sort(Text.Split(_,",")),",")})
in
    #"Sort Column"

当然可以实现，问题出在你只处理了单列，且默认的List.Sort是按文本排序（会出现"10"排在"2"前面的错误）。下面是修改后的代码，能同时对两列做数值排序：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Case GTIN Number"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Attribute]), "Attribute", "Value",each Text.Combine(_, ",")),
    #"Sort Multiple Columns" = Table.TransformColumns(#"Pivoted Column", {
        {"Warehouse Number", (input) => 
            Text.Combine(
                List.Sort(
                    Text.Split(input, ","), 
                    (a,b) => Value.Compare(Number.From(a), Number.From(b))
                ), 
                ","
            )
        },
        {"Division #", (input) => 
            Text.Combine(
                List.Sort(
                    Text.Split(input, ","), 
                    (a,b) => Value.Compare(Number.From(a), Number.From(b))
                ), 
                ","
            )
        }
    })
in
    #"Sort Multiple Columns"

核心改动：

• 在Table.TransformColumns中同时定义两列的处理逻辑，避免互相干扰
• 给List.Sort添加自定义排序规则，先把文本转成数值再比较，实现真正的数值排序

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