如何用Power Query将非表格化分组数据合并为单个表格？

核心思路 #

利用Power Query不修改原表的特性，通过「定位分组标识→匹配归属分组→重构表格结构」三个核心步骤，自动适配分组新增、移动的场景，无需手动调整规则。

具体操作步骤 #

1. 导入数据到Power Query #

• 选中原工作表任意单元格，点击「数据」选项卡 → 「从表格/区域」，弹出窗口中根据原数据情况勾选「我的表格有标题」，点击确定进入Power Query编辑器。
• 选择「仅创建连接」，避免改动共享文档的原结构。

2. 标记单元格位置并过滤空值 #

添加自定义列标记每个非空单元格的行号、列号，方便后续识别分组边界：

= Table.AddColumn(源, "行号", each Table.PositionOf(源, _)[0]+1)
= Table.AddColumn(前一步骤, "列号", each Table.PositionOf(源, _)[1]+1)
= Table.SelectRows(前一步骤, each not List.IsEmpty(List.RemoveNulls(Record.FieldValues(_))))

3. 识别分组名称与归属 #

基于分组名称是「唯一字母数字组合」的特性，先定位所有分组名称，再为每个数据行匹配所属分组：

• 添加自定义列判断单元格是否为分组名称（可根据实际规则调整，比如排除带空格的文本）：

= Table.AddColumn(前一步骤, "是否为分组名", each Value.Is([值], type text) and Text.Remove([值], {"0".."9", "a".."z", "A".."Z"}) = "")

• 提取分组名称的位置信息，生成映射表：

分组映射表 = Table.SelectRows(前一步骤, each [是否为分组名] = true)[[行号],[列号],[值]]
= Table.RenameColumns(分组映射表, {{"值", "分组名称"}})

• 为每个数据行匹配对应分组（同一分组内行差不超过5，列差不超过分组最大列数，可按需调整数值）：

= Table.AddColumn(前一步骤, "分组名称", (x) => 
    let
        候选分组 = Table.SelectRows(分组映射表, each [行号] <= x[行号] and x[行号] - [行号] <= 5 and [列号] <= x[列号] and x[列号] - [列号] <= 5)
    in
        if Table.RowCount(候选分组) > 0 then List.Last(候选分组[分组名称]) else null
)

4. 重构表格结构 #

• 移除多余辅助列：

= Table.RemoveColumns(前一步骤, {"行号", "列号", "是否为分组名"})

• 按分组名称聚合，将每组数据转成结构化行：

= Table.Group(前一步骤, {"分组名称"}, {{"分组数据", each Table.Pivot(Table.AddIndexColumn(_, "行索引", 1, 1), List.Distinct(_[列名]), "列名", "值")}})

注：若分组无固定列标题，可将「列名」替换为按行索引生成的「列1」「列2」等标识

• 展开所有分组数据，得到统一表格：

= Table.ExpandTableColumn(前一步骤, "分组数据", Table.ColumnNames(前一步骤[分组数据]{0}))

5. 加载并设置自动刷新 #

• 点击「关闭并上载」，选择「仅创建连接」后加载到新工作表。
• 右键新表格→「连接属性」，勾选「打开文件时刷新数据」，确保分组新增/移动后自动更新结果。

适配场景说明 #

• 新增分组：只要分组名称符合字母数字唯一规则，Power Query会自动识别并纳入转换。
• 分组移动：基于单元格位置动态匹配分组，移动后无需调整规则，刷新即可更新结果。
• 共享文档安全：全程仅读取原表数据，不修改原结构，不影响其他协作者。

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