如何使用Excel Power Query将指定JSON文件转为表格或列？

一、导入JSON文件到Power Query #

打开Excel，点击「数据」选项卡 → 「获取数据」→ 「从文件」→ 「从JSON」，选中你的目标JSON文件。导入后会看到JSON的根结构，包含fields、items、relatedObjects三个核心节点。

二、把主数据（items）转成结构化表格 #

1. 找到items列，点击右侧的双箭头图标，选择「扩展到新行」，把每个item拆成单独一行。
2. 处理values列：
   • 注意根节点的fields是id, name, definition，刚好对应每个values里的三个值。
   • 点击values列的双箭头，选「提取值」，分隔符用逗号，之后选中提取后的列，点「转换」→ 「拆分列」→ 「按分隔符」，用逗号拆分出3列，最后把这三列重命名为id, name, definition。
3. 处理关联关系（按需操作）：
   • 点击relationships列的双箭头，选「扩展到新行」，拆分每个关联项。
   • 展开toTable列拿到关联表格名称，再展开子节点的relationships列，提取里面的id值，重命名为「关联ID」。

三、解析关联对象（relatedObjects）为独立表格 #

1. 返回Power Query的初始根数据，点击relatedObjects列的双箭头，选「扩展到新行」。
2. 展开TableId列拿到关联表名称；展开items列到新行；再展开items里的values列。
3. 根据每个relatedObjects里的fields字段，给拆分后的values列重命名，比如Table 1的fields是id, Number, name，就把拆分后的列对应改成这些名字。

四、主表和关联表的整合（可选） #

如果需要把主表和关联数据关联起来，用「合并查询」功能：

• 选中主表和关联表，选择两者匹配的id字段作为键，合并类型选左外连接即可。

示例JSON结构 #

{ 
  "fields": [ "id", "name", "definition" ], 
  "items": [ 
    { 
      "ref": "#28:1", 
      "id": "1", 
      "values": [ "1", "ABC", "This is file 1." ], 
      "relationships": [ 
        { "toTable": "Table 1", "relationships": [ { "id": "1", "extra": {} } ] }, 
        { "toTable": "Table 2", "relationships": [ { "id": "1", "extra": {} }, { "id": "7", "extra": {} }, { "id": "11", "extra": {} }, { "id": "24", "extra": {} } ] }, 
        { "toTable": "Table 3", "relationships": [ { "id": "22", "extra": {} }, { "id": "31", "extra": {} } ] }, 
        { "toTable": "Table 4", "relationships": [ { "id": "37", "extra": {} }, { "id": "38", "extra": {} }, { "id": "50", "extra": {} } ] } 
      ] 
    }, 
    { 
      "ref": "#28:2", 
      "id": "2", 
      "values": [ "2", "DEF", "This is file 2." ], 
      "relationships": [ 
        { "toTable": "Table 1", "relationships": [ { "id": "3", "extra": {} } ] }, 
        { "toTable": "Table 2", "relationships": [ { "id": "1", "extra": {} }, { "id": "5", "extra": {} }, { "id": "24", "extra": {} } ] }, 
        { "toTable": "Table 3", "relationships": [ { "id": "1", "extra": {} } ] }, 
        { "toTable": "Table 4", "relationships": [ { "id": "5", "extra": {} }, { "id": "7", "extra": {} } ] } 
      ] 
    }, 
    { 
      "ref": "#28:3", 
      "id": "3", 
      "values": [ "3", "GHI", "This is file 3." ], 
      "relationships": [ 
        { "toTable": "Table 1", "relationships": [ { "id": "1", "extra": {} } ] }, 
        { "toTable": "Table 2", "relationships": [ { "id": "2", "extra": {} }, { "id": "5", "extra": {} }, { "id": "8", "extra": {} } ] }, 
        { "toTable": "Table 4", "relationships": [ { "id": "5", "extra": {} }, { "id": "8", "extra": {} } ] } 
      ] 
    } 
  ], 
  "relatedObjects": [ 
    { 
      "TableId": "Table 1", 
      "totalItems": 151, 
      "totalHits": 1, 
      "fields": [ "id", "Number", "name" ], 
      "items": [ { "ref": "#165:28", "id": "1", "values": [ "1", "ASRU" ] } ] 
    }, 
    { 
      "TableId": "Table 2", 
      "totalItems": 282, 
      "totalHits": 5, 
      "fields": [ "id", "fullName", "firstName" ], 
      "items": [ { "ref": "#68:83", "id": "5", "values": [ "5", "ABC", "Acer" ] } ] 
    } 
  ] 
}

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