Power Query转JSON时如何结构化foo数组且保留bar字段？

问题描述 #

现有如下Power Query记录：

Data = [
        tracker = {
            [
                foo = {
                        [field_a= "Something", field_b = "data2"],
                        [field_a= "Something", field_c = "data2"]
                },
                bar = "Data"
            ],
            [
                foo = {
                        [field_c= "Something", field_b = "data2"],
                        [field_a= "Something", field_c = "data2"]
                },
                bar = "Data2"
            ]
        } 
    ]

需要将foo数组内的每个对象补全field_a、field_b、field_c字段，缺失字段设为null，同时保留bar字段。之前尝试的代码导致bar字段丢失。

解决方案 #

核心思路：仅对每个tracker项中的foo字段做转换处理，原记录的其他字段（如bar）保持不变。以下是完整的Power Query代码：

let
    Data = [
        tracker = {
            [
                foo = {
                        [field_a= "Something", field_b = "data2"],
                        [field_a= "Something", field_c = "data2"]
                },
                bar = "Data"
            ],
            [
                foo = {
                        [field_c= "Something", field_b = "data2"],
                        [field_a= "Something", field_c = "data2"]
                },
                bar = "Data2"
            ]
        } 
    ],
    // 遍历tracker列表，处理每个项的foo字段，保留其他字段
    ProcessedTracker = List.Transform(Data[tracker], each 
        Record.Combine({
            _, // 保留原记录中的bar等所有字段
            [foo = List.Transform([foo], each 
                // 给foo内的每个记录补全三个字段，缺失字段设为null并执行Text.Trim
                Record.TransformFields(_, {
                    {"field_a", Text.Trim},
                    {"field_b", Text.Trim},
                    {"field_c", Text.Trim}
                }, MissingField.UseNull)
            )]
        })
    ),
    // 重组为原始结构
    Result = [tracker = ProcessedTracker]
in
    Result

代码说明 #

1. List.Transform(Data[tracker], ...)：遍历tracker列表中的每个记录项。
2. Record.Combine({_, [foo=...]})：将原记录（包含bar字段）与处理后的foo字段合并，确保bar字段被保留。
3. List.Transform([foo], ...)：对每个foo数组内的记录进行字段补全，通过MissingField.UseNull参数自动为缺失字段添加null值，同时对存在的字段执行Text.Trim处理。

转换后示例（JSON格式） #

处理后导出的JSON将保留bar字段，且foo内的每个对象都包含三个字段：

{
  "tracker": [
    {
      "foo": [
        {
          "field_a": "Something",
          "field_b": "data2",
          "field_c": null
        },
        {
          "field_a": "Something",
          "field_b": null,
          "field_c": "data2"
        }
      ],
      "bar": "Data"
    },
    {
      "foo": [
        {
          "field_a": null,
          "field_b": "data2",
          "field_c": "Something"
        },
        {
          "field_a": "Something",
          "field_b": null,
          "field_c": "data2"
        }
      ],
      "bar": "Data2"
    }
  ]
}

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