使用Power Query展开JSON多记录列表的问题求助

问题说明 #

原始JSON结构 #

{
  "data": {
    "assets": [
      {
        "asset": "apple",
        "metrics": [
          {
            "value": 2,
            "metric": "weight"
          },
          {
            "value": "red",
            "metric": "color"
          }
        ]
      },
      {
        "asset": "banana",
        "metrics": [
          {
            "value": "yellow",
            "metric": "color"
          },
          {
            "value": 3,
            "metric": "weight"
          }
        ]
      }
    ]
  }
}

Power Query初始加载状态 #

加载后得到如下表格：

其中details列的每个List包含多个Record，单个Record结构如下：

遇到的问题 #

• 对details列使用「提取值」功能时，触发错误：Expression.Error: We cannot convert a value of type Record to type Text.
• 直接展开List为新行后，得到同一asset对应多行数据的表格：

期望结果 #

需要将数据转换为宽表格式：

解决方案 #

方法1：界面可视化操作 #

• 选中details列，切换到「转换」选项卡
• 在「结构」组中点击「转列」→「值列转列」
• 在弹出的配置窗口中：
  • 选择metricName作为「键列」
  • 选择value作为「值列」
• 点击确定，即可自动生成目标宽表

方法2：M代码手动实现 #

如果界面操作受限，可直接编写M代码完成转换：

let
    // 替换为你的数据源加载步骤
    Source = Json.Document(Web.Contents("你的JSON文件路径")),
    Navigation = Source[data][assets],
    ConvertToTable = Table.FromList(Navigation, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandAssets = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"asset", "metrics"}, {"asset", "details"}),
    // 展开details列的List
    ExpandDetails = Table.ExpandListColumn(ExpandAssets, "details"),
    // 展开Record字段并匹配字段名
    ExpandRecords = Table.ExpandRecordColumn(ExpandDetails, "details", {"metric", "value"}, {"metricName", "value"}),
    // 转置为宽表
    PivotTable = Table.Pivot(ExpandRecords, List.Distinct(ExpandRecords[metricName]), "metricName", "value")
in
    PivotTable

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