如何用Power Query将堆叠列转为键值对并生成逻辑表格？

核心思路 #

因为数据是键值对堆叠且存在值缺失，固定长度拆分（比如List.Split(...,6)）必然失效。正确逻辑是先按「记录起始标记」把数据拆分成独立的记录组，再将每组内的键值对转换为结构化表格。

分步实现（假设起始键为「姓名」，可自行替换） #

1. 加载数据并添加索引

先把原始列（如ColumnA）导入Power Query，添加索引列用于定位：

let
    // 替换为你的数据源（比如Excel结构化表格）
    源 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // 添加从0开始的索引列
    添加索引 = Table.AddIndexColumn(源, "索引", 0, 1, Int64.Type)
in
    添加索引

2. 标记每个记录的ID

找出所有起始键的位置，为每一行分配对应的记录ID：

let
    源 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    添加索引 = Table.AddIndexColumn(源, "索引", 0, 1, Int64.Type),
    // 获取所有起始键的索引位置
    起始位置 = List.PositionOf(添加索引[ColumnA], "姓名", Occurrence.All),
    // 给每行分配所属的记录ID
    添加记录ID = Table.AddColumn(添加索引, "记录ID", (行) => 
        List.Count(List.Select(起始位置, (x) => x <= 行[索引])) - 1
    )
in
    添加记录ID

3. 按记录ID分组

将同一记录的行归为一组，每组得到一个键值对列表：

let
    源 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    添加索引 = Table.AddIndexColumn(源, "索引", 0, 1, Int64.Type),
    起始位置 = List.PositionOf(添加索引[ColumnA], "姓名", Occurrence.All),
    添加记录ID = Table.AddColumn(添加索引, "记录ID", (行) => 
        List.Count(List.Select(起始位置, (x) => x <= 行[索引])) - 1
    ),
    // 按记录ID分组，保留键值对列表
    分组记录 = Table.Group(添加记录ID, {"记录ID"}, {{"键值对列表", each _[ColumnA], type list}})
in
    分组记录

4. 将每组键值对转成记录

遍历每个组的列表，自动配对键和值，缺失值设为空：

let
    源 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    添加索引 = Table.AddIndexColumn(源, "索引", 0, 1, Int64.Type),
    起始位置 = List.PositionOf(添加索引[ColumnA], "姓名", Occurrence.All),
    添加记录ID = Table.AddColumn(添加索引, "记录ID", (行) => 
        List.Count(List.Select(起始位置, (x) => x <= 行[索引])) - 1
    ),
    分组记录 = Table.Group(添加记录ID, {"记录ID"}, {{"键值对列表", each _[ColumnA], type list}}),
    // 把键值对列表转换为Power Query记录
    转成记录 = Table.AddColumn(分组记录, "记录", (行) => 
        let
            列表 = 行[键值对列表],
            // 生成键值对：每次取两个元素，最后一个键无值则设为空
            键值对 = List.Generate(
                () => [索引=0, 键=List.Item(列表,0), 值=if List.Count(列表)>1 then List.Item(列表,1) else null],
                each [索引] < List.Count(列表),
                each [索引=[索引]+2, 键=if [索引] <= List.Count(列表)-1 then List.Item(列表,[索引]) else null, 值=if [索引]+1 <= List.Count(列表)-1 then List.Item(列表,[索引]+1) else null],
                each if [键] <> null then [键=[键], 值=[值]] else null
            ),
            清理空值 = List.RemoveNulls(键值对),
            最终记录 = Record.FromList(List.Transform(清理空值, each _[值]), List.Transform(清理空值, each _[键]))
        in
            最终记录
    ),
    // 提取记录列转换为结构化表格
    转成表格 = Table.FromRecords(转成记录[记录])
in
    转成表格

适配每日新增数据 #

• 原始数据用Excel结构化表格（Ctrl+T创建），新增数据直接追加到表格末尾，Power Query刷新时会自动加载新内容。
• 若起始键不是「姓名」，直接替换代码中"姓名"为你的实际起始键即可。
• 若存在多类型起始键，可修改起始位置逻辑，用List.Contains({"键1","键2"}, 行[ColumnA])判断是否为起始键。

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