CODEWITHSUNDEEP
excelpowerquery
MrChrisP
MrChrisP

Reputation: 17

Problem with Dynamic column names in Power Query Editor

I am using Power Query to pull data from an API into an Excel table. This is a dynamic API and the date range can be specified. For example the report is generally run at the beginning of a month for the previous month (Eg. 01/06/2020 00:00 - 01/07/2020 00:00)

The API returns the average windspeed in the specified date range for a number of different sites.

My problem is that the data column name changes in the API depending on the date range specified. For example if I run it to pull data for June 2020 when I expend the column I get this:

= Table.ExpandRecordColumn(#"Expanded Column1", "data", {"2020-06-01T00:00:00"}, {"2020-06-01T00:00:00"})

This results in the column name being 2020-06-01T00:00:00

I then merge several queries together and at this point everything looks to be working fine. However if I change the date range to look at May for example, the Query keeps the column title of 2020-06-01T00:00:00 , however the actual data has come in with column title of 2020-05-01T00:00:00

As a result the average windspeeds are not returned but instead all values are replaced with Null.

My guess is that this is a fundamental issue with the API call being used, but is anyone aware of a way to stop this issue from happening?

Many thanks

Upvotes: 0

Views: 469

Answers (1)

Alex
Alex

Reputation: 136

If your API output looks something like this:

{
"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
    }
]
}

convert the source into a table first. This will make your date key(s) a value in its own column. Example:

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"

Update: Base on the JSON sample provided, the below should work. The key here is also to use the Record.ToTable() function

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"

Upvotes: 1

Related Questions