Excel Power Query 合并后表格加序号后缀展开为列的实现方法

操作专业术语 #

该操作的通用专业术语为横向行转列展开，针对嵌套表场景也常被称为嵌套表宽格式化，你在搜索Power Query相关教程时使用这两个关键词即可找到对应内容。

Power Query 实现方案 #

你无需依赖VBA，Power Query原生支持该需求，以下是两种实现路径：

路径1：纯界面操作（无需写M代码） #

• 点击items嵌套列右侧的扩展按钮，选择「扩展到新行」，先把所有商品数据展开为行格式
• 按销量字段降序排序（如果原表已有排名字段就按排名升序排序），添加索引列作为商品序号
• 筛选序号列，保留你需要的前X名商品
• 新增自定义列，拼接字段名和序号：=[列名]&"_"&Text.From([序号])，如果要处理多个列，就给每个需要展开的列都做一次拼接
• 选中date、total_sales等固定列，选择「透视列」功能，值列选你要展示的商品、销量等字段，聚合方式选择「不要聚合」，即可直接生成目标宽表。

路径2：M代码实现（效率更高，适合大表） #

你可以直接在高级编辑器中修改M代码，核心逻辑是先给嵌套表加序号再转成带后缀的记录再展开，示例代码片段如下：

// 替换为你合并后的表名
let
    源 = 你的合并后结果表,
    // 给嵌套商品表加序号，保留前2名（可自行修改X的数值）
    加商品序号 = Table.AddColumn(源, "带序号商品", (row) => 
        let
            排序 = Table.Sort(row[items], {"销量", Order.Descending}),
            加索引 = Table.AddIndexColumn(排序, "序号", 1, 1, Int64.Type),
            筛选前X = Table.SelectRows(加索引, each [序号] <= 2)
        in
            筛选前X
    ),
    // 嵌套表转带后缀的记录
    转展开记录 = Table.AddColumn(加商品序号, "展开记录", (row) => 
        let
            商品列表 = Table.ToRows(Table.RemoveColumns(row[带序号商品], "序号")),
            列名列表 = Table.ColumnNames(Table.RemoveColumns(row[带序号商品], "序号")),
            序号列表 = row[带序号商品][序号],
            新列名 = List.TransformMany(序号列表, (n) => List.Transform(列名列表, (c) => c & "_" & Text.From(n))),
            新值列表 = List.Combine(商品列表)
        in
            Record.FromList(新值列表, 新列名)
    ),
    // 展开记录得到最终宽表
    结果 = Table.ExpandRecordColumn(转展开记录, "展开记录", List.Distinct(List.Combine(Table.Transform(转展开记录[展开记录], Record.FieldNames))))
in
    结果

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