在Power Query中构建数据模型：映射字段列表至嵌套JSON子集

核心思路 #

通过Power Query的动态关联与透视能力，自动匹配自定义字段ID和友好名称，同时支持数据源刷新时新增字段的自动识别，无需静态配置。

步骤1：加载并预处理数据源 #

• 加载工单主数据：导入工单JSON后，找到custom_data_fields列，点击展开按钮选择"展开到新行"，将每个自定义字段拆分为单独行，此时表结构为：工单id、subject、custom_field_id、custom_field_value（根据实际JSON结构调整字段名）。
• 加载字段定义数据：导入字段定义JSON，整理成包含field_id（对应custom_field_id）和field_name（如Location、Department）的二维表，确保该表能随数据源刷新自动获取新增字段。

步骤2：关联工单与字段定义 #

在展开后的工单表中执行合并查询：

1. 点击"合并查询"，选择字段定义表作为关联对象
2. 关联条件选择工单表的custom_field_id与字段定义表的field_id
3. 展开合并后的列，仅保留field_name字段
   此时表结构更新为：工单id、subject、field_name、custom_field_value

步骤3：动态生成宽表（报表用户首选） #

通过透视列自动将字段名转为列标题，支持新增字段自动识别：

1. 选中field_name列，点击"转换"选项卡的"透视列"
2. 值列选择custom_field_value，聚合函数选择"不要聚合"（确保每个工单每个字段唯一值）
3. 完成后表结构会变为：工单id、subject、Location、Department... 新增字段在数据源刷新后会自动出现在表中

对应的Power Query M代码片段：

Table.Pivot(
    #"Merged Field Definitions", // 替换为你的合并后查询名称
    List.Distinct(#"Merged Field Definitions"[field_name]),
    "field_name",
    "custom_field_value",
    List.First // 单值场景用这个，多值可替换为Text.Combine
)

步骤4：备选方案 - 星型数据模型（灵活性更高） #

如果需要保留明细分析能力，无需转宽表，可建立星型模型：

• 事实表：工单自定义字段明细（工单id、field_id、custom_field_value）
• 维度表1：工单主表（工单id、subject）
• 维度表2：字段定义表（field_id、field_name）

在Power BI模型视图中建立关联：

• 工单主表工单id ↔ 事实表工单id
• 字段定义表field_id ↔ 事实表field_id

用户制作报表时，可直接通过字段定义表的field_name筛选、分组，完全无需关注底层ID。

注意事项 #

• 确保custom_field_value的字段类型尽量统一，若存在混合类型，透视前可通过"转换"→"数据类型"统一处理
• 若同一工单同一字段存在多值，需调整透视的聚合函数（如Text.Combine合并文本，List.Sum求和等）
• 数据源刷新时，字段定义表会自动加载新增字段，透视或模型关联会自动适配，无需手动修改

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