Power BI对接CRM JSON API：提取每行列表多记录字段方案

已完成CRM API分页数据获取，但无法将每条记录中userDefinedFields数组的name-value键值对提取为原记录的字段，同时需要处理该列表为空的异常情况。

API响应示例 #

{
  "items": [
    {
      "id": "RECORD-001",
      "name": "客户A",
      "userDefinedFields": [
        {"name": "客户等级", "value": "VIP"},
        {"name": "跟进频率", "value": "每周"}
      ]
    },
    {
      "id": "RECORD-002",
      "name": "客户B",
      "userDefinedFields": []
    },
    {
      "id": "RECORD-003",
      "name": "客户C",
      "userDefinedFields": [
        {"name": "所属行业", "value": "互联网"}
      ]
    }
  ],
  "nextPageToken": "TOKEN-001"
}

代码迭代过程 #

版本1：基础分页获取（未处理自定义字段） #

仅实现分页拉取数据，userDefinedFields仍为数组格式，无法直接作为字段使用：

let
    BaseUrl = "https://your-crm-api.com/records",
    GetPage = (OptionalToken as nullable text) =>
        let
            Url = if OptionalToken = null then BaseUrl else BaseUrl & "?pageToken=" & OptionalToken,
            Response = Json.Document(Web.Contents(Url)),
            Items = Response[items],
            NextToken = try Response[nextPageToken] otherwise null
        in
            [Items = Items, NextToken = NextToken],
    Pages = List.Generate(
        () => GetPage(null),
        each [NextToken] <> null,
        each GetPage([NextToken]),
        each [Items]
    ),
    CombinedRecords = List.Combine(Pages)
in
    CombinedRecords

版本2：尝试提取自定义字段（无错误处理） #

尝试将userDefinedFields转为记录，但未处理空数组场景，会触发报错导致数据集加载失败：

// 在CombinedRecords后追加代码
    ExpandUDF = Table.FromRecords(CombinedRecords),
    ConvertUDF = Table.AddColumn(ExpandUDF, "UDF_Record", each Record.FromList(List.Transform([userDefinedFields], each _[value]), List.Transform([userDefinedFields], each _[name])))
in
    ConvertUDF

版本3：最终可用代码（含错误处理） #

通过错误捕获处理空数组，同时统一提取所有自定义字段并展开，保证数据结构一致性：

let
    BaseUrl = "https://your-crm-api.com/records",
    GetPage = (OptionalToken as nullable text) =>
        let
            Url = if OptionalToken = null then BaseUrl else BaseUrl & "?pageToken=" & OptionalToken,
            Response = Json.Document(Web.Contents(Url)),
            Items = Response[items],
            NextToken = try Response[nextPageToken] otherwise null
        in
            [Items = Items, NextToken = NextToken],
    Pages = List.Generate(
        () => GetPage(null),
        each [NextToken] <> null,
        each GetPage([NextToken]),
        each [Items]
    ),
    CombinedRecords = List.Combine(Pages),
    // 转换为表结构
    ConvertToTable = Table.FromRecords(CombinedRecords),
    // 将userDefinedFields转为记录，空数组返回空记录避免报错
    CreateUDFRecord = Table.AddColumn(ConvertToTable, "UDF_Record", 
        each try Record.FromList(
            List.Transform([userDefinedFields], each _[value]),
            List.Transform([userDefinedFields], each _[name])
        ) otherwise Record.Empty),
    // 收集所有出现过的自定义字段名，统一展开
    ExpandUDF = Table.ExpandRecordColumn(CreateUDFRecord, "UDF_Record", 
        List.Distinct(List.Combine(List.Transform(CreateUDFRecord[UDF_Record], Record.FieldNames))),
        List.Distinct(List.Combine(List.Transform(CreateUDFRecord[UDF_Record], Record.FieldNames)))
    ),
    // 清理临时列
    CleanUp = Table.RemoveColumns(ExpandUDF, {"userDefinedFields", "UDF_Record"})
in
    CleanUp

关键处理说明 #

• 用try...otherwise Record.Empty捕获空数组场景，避免报错中断加载
• 通过List.Distinct(List.Combine(...))遍历所有记录的自定义字段名，确保展开时不会遗漏任何字段
• 展开后缺失字段自动填充null，保证全量数据结构一致

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