PowerQuery实现BOM表格全组合（笛卡尔积）的方法咨询

问题场景 #

原表数据：

需要生成每个Product下所有BOMproduct的全排列，将每个排列的元素对应到原表的每一行，新增All_combinations字段，最终得到9行的结果表。

实现方案（M代码） #

直接用以下M代码可以完成需求，步骤拆解如下：

1. 按Product分组，把相同产品的数据归为一组，同时提取每组的BOMproduct列表
2. 用回溯法生成每组BOM列表的所有全排列
3. 将每个排列与原组数据按Bomline的顺序匹配，添加All_combinations字段
4. 展开所有分组的处理结果，清理冗余列

完整M代码：

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], // 替换成你的实际数据源，比如Excel表名
    // 按Product分组，保留组内数据和BOMproduct列表
    分组处理 = Table.Group(源, {"Product"}, {
        {"组内数据", each _, type table [Product=text, Bomline=number, BOMproduct=text]},
        {"BOM列表", each [BOMproduct], type list}
    }),
    // 对每个分组生成全排列并关联原行
    生成排列表 = Table.AddColumn(分组处理, "排列结果", each 
        let
            // 回溯法生成BOM列表的全排列
            全排列集合 = List.Generate(
                () => [当前排列=[], 可用元素=_[BOM列表]],
                each List.Count([当前排列]) < List.Count(_[BOM列表]) or not List.IsEmpty([可用元素]),
                each if List.IsEmpty([可用元素]) then
                    [
                        当前排列 = List.RemoveLastN([当前排列], 1),
                        可用元素 = _[可用元素] & {List.Last([当前排列])}
                    ]
                else
                    [
                        当前排列 = [当前排列] & {List.First([可用元素])},
                        可用元素 = List.RemoveFirstN([可用元素], 1)
                    ],
                each if List.Count([当前排列]) = List.Count(_[BOM列表]) then [当前排列] else null
            ),
            // 过滤空值，得到有效排列
            有效排列 = List.RemoveNulls(全排列集合),
            // 把每个排列转成带索引的表，索引对应Bomline
            排列转表 = List.Transform(有效排列, (perm) => 
                Table.FromColumns({perm}, {"All_combinations"})
                |> Table.AddIndexColumn("匹配索引", 1, 1, Int64.Type)
            ),
            // 每个排列表和原组数据按Bomline匹配
            合并每行 = List.Transform(排列转表, (t) => 
                Table.NestedJoin(_[组内数据], {"Bomline"}, t, {"匹配索引"}, "临时表", JoinKind.Inner)
                |> Table.ExpandTableColumn("临时表", {"All_combinations"}, {"All_combinations"})
            ),
            // 合并所有排列对应的表
            组内合并 = Table.Combine(合并每行)
        in
            组内合并
    ),
    // 展开所有分组的结果
    展开所有 = Table.ExpandTableColumn(生成排列表, "排列结果", {"Product", "Bomline", "BOMproduct", "All_combinations"}),
    // 清理重复的Product列
    最终表 = Table.RemoveColumns(展开所有, {"Product.1"})
in
    最终表

注意事项 #

• 替换代码中的源部分为你的实际数据源（比如Excel表、CSV等）
• 如果你的Bomline不是连续的1、2、3，只要是按顺序的唯一数值，代码依然有效，因为是通过索引匹配顺序
• 当Product有多个时，代码会自动按每个Product单独生成全排列组合

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