excel - 如何使用 Power Query 编辑器在 Office Excel 中正确显示数据？

Question

我有以下来自 API 的 JSON 输出，在 Office Excel 中我通过 Web 从 API 导入数据。

   [{
        "level": 1,
        "children": [{
            "level": 2,
            "children": [{
                "level": 3,
                "name": "Chandni Chowk",
                "data": ["Data 1", "Data 2"]
            }],
            "name": "Delhi",
            "data": ["Delhi Area"]
        }],
        "name": "Country",
        "data": ["India", "Bangladesh"]
    }]

https://docs.microsoft.com/en-us/powerquery-m/quick-tour-of-the-power-query-m-formula-language
我有上面的文件。

let
    Source = Json.Document(Web.Contents("MY API URL GOES HERE")),
    AsTable = Table.FromRecords(Source)
    ----
    ----
in
    #"Renamed Column2" 

在电源查询编辑器中，我现在有这个。
作为 Excel 文件的输出，我需要这样。

Country       Delhi          Chandni Chowk         
India         Delhi Area     Data 1
Bangladesh                   Data 2

我可以从此 JSON 中获取此数据，还是需要更改与 power 查询匹配的 JSON 输出格式？

Answer 1

Power Query 将 JSON 解释为记录和列表的层次结构。我的目标是将 JSON 展平为这样的记录，然后将其转换为表格:

      Country : {"India", "Bangladesh"}
        Delhi : {"Delhi Area"}
Chandni Chowk : {"Data 1", "Data 2"}

在任何特定级别，我们都可以使用 Record.FromList 提取名称和数据值。 :

Record.FromList({CurrentLevel[data]}, {CurrentLevel[name]})

对于第一级，这是

Record.FromList({{"India","Bangladesh"}}, {"Country"})

对应于目标记录中的第一个字段。

在任何级别，我们都可以像这样导航到下一个级别:

NextLevel = CurrentLevel[children]{0}

使用这些构建 block ，我们现在可以编写自定义函数 Expand压平记录:

1 |    (R as record) as record =>
2 |    let
3 |        ThisLevel = Record.FromList({R[data]}, {R[name]}),
4 |        CombLevel = if Record.HasFields(R, {"children"})
5 |                    then Record.Combine({ThisLevel, @Expand(R[children]{0})})
6 |                    else ThisLevel
7 |    in
8 |        CombLevel

第 1 行:定义函数的语法。记录 R并在进行一些转换后返回一条记录。
第 3 行:如何处理当前级别，如前所述。
第 4 行:检查记录是否有另一个级别可以向下扩展。
第 5 行:如果是，则 Record.Combine当前级别的结果与下一个级别的结果，其中下一个级别的结果是通过导航到下一个级别并递归应用我们定义的函数来计算的。这看起来像三个级别:

Record.Combine({Level1, Record.Combine({Level2, Level3})})

第 6 行:当没有更多级别要扩展时，递归停止。没有更多的组合，只返回最后一个级别。

剩下的就是把它变成我们想要的形状。下面是我的查询使用我们刚刚定义的 Expand 函数时的样子:

let
    Source = Json.Document( < JSON Source > ),
    ExpandRecord = Expand(Source{0}),
    ToTable = Table.FromColumns(
                  Record.FieldValues(ExpandRecord),
                  Record.FieldNames(ExpandRecord)
              )
in
    ToTable

这使用 Record.FieldValues和 Record.FieldName作为 Table.FromColumns 中的参数.
使用 Expand后的步骤如果您选择第一个列表单元格，则自定义函数在查询编辑器中如下所示:

最终结果是您要求的: