excel - Power Query 编辑器中的动态列名问题

Question

我正在使用 Power Query 将数据从 API 提取到 Excel 表中。这是一个动态 API，可以指定日期范围。例如，报告通常在上个月的月初运行（例如 01/06/2020 00:00 - 01/07/2020 00:00）

API 返回多个不同站点在指定日期范围内的平均风速。

我的问题是 API 中的数据列名称会根据指定的日期范围发生变化。例如，如果我在扩展列时运行它以提取 2020 年 6 月的数据，我会得到：

= Table.ExpandRecordColumn(#"扩展列1", "数据", {"2020-06-01T00:00:00"}, {"2020-06-01T00:00:00"})

这导致列名称为2020-06-01T00:00:00

然后我将几个查询合并在一起，此时一切看起来都很好。但是，如果我将日期范围更改为查看 5 月，则查询会保留2020-06-01T00:00:00的列标题，但实际数据已包含2020-05-01T00:00 的列标题： 00

因此，不会返回平均风速，而是将所有值替换为 Null。

我的猜测是，这是正在使用的 API 调用的一个基本问题，但是有人知道阻止这个问题发生的方法吗？

非常感谢

标签： excelpowerquery

Answer 1

如果您的 API 输出如下所示：

{
"2020-06-01T00:00:00": [
    {
        "site": 111,
        "avgSpeed": 12.5
    },
    {
        "site": 112,
        "avgSpeed": 12.5
    },
    {
        "site": 113,
        "avgSpeed": 15.5
    },
    {
        "site": 114,
        "avgSpeed": 25.4
    }
]
}

首先将源转换为表格。这将使您的日期键在其自己的列中成为一个值。例子：

let
    Source = Json.Document(Web.Contents("https://....")),
    RecordToTable = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(RecordToTable, "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"site", "avgSpeed"}, {"site", "avgSpeed"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Value1",{{"Name", "Date"}})
in
    #"Renamed Columns"

更新： 基于提供的 JSON 示例，以下应该可以工作。这里的关键也是要使用 Record.ToTable() 函数

let
    Source = Json.Document(Web.Contents("https://....")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"aggregate", "aggregatePathNames", "aggregateId", "deviceIds", "resolution", "calculation", "dataSignal", "data"}, {"aggregate", "aggregatePathNames", "aggregateId", "deviceIds", "resolution", "calculation", "dataSignal", "data"}),
    RecordsToTable = Table.AddColumn(#"Expanded Column1", "data_table", each Record.ToTable([data])),
    #"Expanded data_table" = Table.ExpandTableColumn(RecordsToTable, "data_table", {"Name", "Value"}, {"Name", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded data_table",{{"Name", "Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"data"}),
    #"Expanded dataSignal" = Table.ExpandRecordColumn(#"Removed Columns", "dataSignal", {"dataSignalId", "title", "unit"}, {"dataSignalId", "title", "unit"})
in
    #"Expanded dataSignal"