如何在Power Query中解析冒号分隔的键值对列表并生成表格

针对你给出的按空白行分组的冒号分隔键值对文本，以下是具体实现步骤：

步骤1：导入原始文本到Power Query #

• 打开Excel，新建空白查询（数据选项卡 → 获取数据 → 从其他源 → 空白查询），进入Power Query编辑器
• 点击主页选项卡的高级编辑器，替换默认代码为以下内容（把原始文本粘贴进去）：

let
    原始文本 = "        name: david
 spouse name: rachel 
        kids: 3
        
        name: ronald
 spouse name: dilda
        kids: 2 ",
    转换为列表 = Text.Split(原始文本, "#(lf)")
in
    转换为列表

这段代码会把原始文本按换行符拆分成单行列表，方便后续逐行处理。

步骤2：清洗无效行 #

• 点击添加列 → 自定义列，输入公式清洗每行的前后空格：

  Text.Trim([Column1])
  

• 把新列重命名为清洗后行，然后筛选掉空行：选中清洗后行列，点击表头的筛选按钮，取消勾选(空白)。

步骤3：给每组数据标记唯一ID #

因为原始数据是按空白行分隔成组，现在空白行已被过滤，我们通过name:开头的行来标记分组：

• 添加自定义列分组标记，公式：

  if Text.StartsWith([清洗后行], "name:") then 1 else 0
  

• 添加索引列（添加列 → 索引列 → 从0开始）
• 再添加自定义列组ID，用累计求和给每组分配唯一编号：

  List.Sum(List.Range(分组标记, 0, [Index]+1))
  

• 删除索引列和分组标记列，只保留清洗后行和组ID。

步骤4：将组内键值对转成结构化表格 #

• 按组ID分组：点击转换 → 分组依据，设置如下：
  • 分组依据：组ID
  • 新列名：组内数据
  • 操作：所有行
• 对组内数据列添加自定义列，把每组的键值对拆分成记录：

  Record.FromList(
      List.Transform([组内数据][清洗后行], each Text.Trim(Text.Split(_, ":"){1})),
      List.Transform([组内数据][清洗后行], each Text.Trim(Text.Split(_, ":"){0}))
  )
  

• 删除组内数据列，把新列重命名为记录，然后点击该列表头右侧的扩展按钮，选择所有字段展开，就能得到包含name、spouse name、kids的列。

步骤5：调整最终格式 #

• 可选：把kids列转为整数类型（选中列 → 转换 → 数据类型 → 整数）
• 关闭Power Query编辑器，加载数据到Excel，即可得到目标结构化表格。

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