Forum Discussion

BrnTan's avatar
BrnTan
Copper Contributor
Nov 13, 2021

Faster way to transform a time range worth of api data in excel query editor and plot a graph

I want to be able to create a trendline graph in excel by combining api data using multiple url sources from https://api.data.gov.sg/v1/environment/wind-speed?date=2021-10-01 (with XX being the days in a date parameter YYYY-MM-DD format) for the whole of Oct 2021 (basically ?date=2021-10-01, ?date=2021-10-02 ... ?date=2021-10-31) as each url can only provide daily "date & time" and "values". Plus, I only require readings from station id S115.

 

Hence, is there a faster way to get and combine all the necessary data, transform it on the query editor as well as plot the graph?

Charting

3 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 13, 2021

    BrnTan 

    If API gives no options I don't think something could be improved dramatically. If only exclude extra json nodes from very beginning and fix converted table in memory.

    let
    Source = Json.Document(
        Web.Contents("https://api.data.gov.sg/v1/environment/wind-speed?date=2021-10-01")
    )[items],
    #"Converted to Table" = Table.Buffer(
        Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    ),
    #"Expand readings lists" = Table.ExpandRecordColumn(
        #"Converted to Table",
        "Column1",
        {"timestamp", "readings"},
        {"timestamp", "readings"}
    ),
    #"Expanded readings" = Table.ExpandListColumn(#"Expand readings lists", "readings"),
    #"Expand readings values" = Table.ExpandRecordColumn(
        #"Expanded readings",
        "readings",
        {"station_id", "value"},
        {"station_id", "value"}
    ),
    #"Filter Station" = Table.SelectRows(
        #"Expand readings values",
        each ([station_id] = "S117")
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        #"Filter Station",
        {
            {"timestamp", type datetimezone},
            {"station_id", type text},
            {"value", type number}
        }
    )
in
    #"Changed Type"

    In theory it could be done with lists transformations, but Power Query is optimised to work with tables.

Resources