通过Web API在Excel中嵌套JSON

Question

我试图从一个嵌套的JSON模式中提取web分析数据，使用Excels Power查询功能从互联网上获取数据。该架构具有以下结构：

// Contents of "~/sites"
{
  "id": 1852274,
  "url": "http://link-to-a-site.com",
  "pages": 10,
  "visits": 1356,
  "_links": {
    "site": {
      "href": "~/sites/1852274"
    }
  },
  (200 entries)
}

// Contents of "~/sites/1852274"
{
  "id": 1852274,
  "url": "http://link-to-a-site.com",
  "_links": {
    "analytics": {
      "overview": {
        "summary": {
          "href": "~/sites/1852274/analytics/overview/summary"
         },
        "groups": {
          "href": "~/sites/1852274/analytics/overview/groups"
        }
      },
      "behavior": {
        "visit_depth": {
          "href": "~/sites/1852274/analytics/behavior/visit_depth"
        },
        "visit_length": {
          "href": "~/sites/1852274/analytics/behavior/visit_length"
        }
      },
     (50 entries)
    }
  }

// Full contents of "~/sites/1852274/analytics/overview/summary
{
  "bounce_rate": 36.36,
  "new_visitors": 6,
  "page_views": 31,
  "returning_visitors": 5,
  "unique_visitors": 11,
  "visits": 11,
}

其中第一模式提供到站点特定数据的链接。所以我的问题是，我如何从~/站点访问链接中引用的数据？

条目太多，无法手动连接到它所引用的站点。

Answer 1

为了提取数据，为了演示，我在JSON文件中添加了第二个条目。

JSON：

{
"ids": [
{
  "id": 1852274,
  "url": "http://link-to-a-site.com",
  "pages": 10,
  "visits": 1356,
  "_links": {
    "site": {
      "href": "~/sites/1852274"
    }
  }
},
{
  "id": 1852274,
  "url": "http://link-to-a-site.com",
  "pages": 10,
  "visits": 1356,
  "_links": {
    "site": {
      "href": "~/sites/1852274"
    }
  }
},
]
}

将其转换为包含href作为列的表的增强查询代码如下：

let
    Source = Json.Document(File.Contents("C:\Users\XXX\Desktop\test.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"id", "url", "pages", "visits", "_links"}, {"id", "url", "pages", "visits", "_links"}),
    #"Expanded _links" = Table.ExpandRecordColumn(#"Expanded Value1", "_links", {"site"}, {"site"}),
    #"Expanded site" = Table.ExpandRecordColumn(#"Expanded _links", "site", {"href"}, {"href"})
in
    #"Expanded site"

​

​