Power Query中可变数量列批量扩展至新行的实现方案

在你现有代码的末尾追加动态扩展逻辑即可，无需手动指定列名，适配任意动态列数的扩展需求，最终生成3528行的组合结果。

完整M代码 #

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYpLCoAwDETvknVhrKB7//9VkMaWXsPzG4oWXAyZlzch0IYdDiIQz3oEzpKhWkPRBGrQo8MMD2bWZ6W5k2mxYFIqv+mBVavNg0Fbken80fVSgvGnhH2W8QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Custom.1 = _t, Custom = _t, variatie = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Custom.1", type text}, {"Custom", Int64.Type}}),
#"Lowercased Text" = Table.TransformColumns(#"Changed Type",{{"variatie", Text.Lower, type text}}),
#"Added Custom" = Table.AddColumn(#"Lowercased Text", "Custom.2", each if [variatie] = "v" then [Custom.1] & "/" else [Custom.1]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Custom.1", "variatie"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom.2", "Custom.1"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "Custom.1", "Custom.1 - Copy"),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"Custom.1 - Copy", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom.1 - Copy"),
#"Grouped Rows" = Table.Group(#"Split Column by Delimiter", {"Custom", "Custom.1"}, {{"Count", each _, type table [Custom=nullable number, Custom.1=nullable text, #"Custom.1 - Copy"=nullable text]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.2", each [Count][#"Custom.1 - Copy"]),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"Custom.1", "Count"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"Custom.2", "Custom.1"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Custom"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Custom.1"}),
#"Transposed Table" = Table.Transpose(#"Reordered Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
// 以下为新增动态扩展逻辑
AllColumns = Table.ColumnNames(#"Promoted Headers"),
// 自动计算所有列的笛卡尔积
CombinationList = List.Accumulate(AllColumns, {{}}, (acc, col)=> 
    List.TransformMany(acc, each Table.Column(#"Promoted Headers", col), (x, y)=> x & {y})
),
// 转换为结构化表
Output = Table.FromRows(CombinationList, AllColumns)
in
Output

逻辑说明 #

• 自动读取当前表所有列名，列数动态增减时无需修改代码，完全符合非手动逐列扩展的要求
• 通过迭代计算所有列的笛卡尔积，最终输出行数为所有列元素数量的乘积，匹配你需要的3528行输出要求

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