PowerBI API: Extract a time-range worth of data and combine

[Joliver]

I want to be able to load data from this api <https://api.data.gov.sg/v1/environment/wind-speed?date=2021-11-01> for a specified time range (e.g. from may 2020 to Sep 2021). The problem is this API url only provides daily "date & time" and "values".

PowerBI advanced editor:
```
let
Source = Json.Document(Web.Contents("https://api.data.gov.sg/v1/environment/wind-speed?date=2021-11-01")),
items = Source[items],
#"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"timestamp", "readings"}, {"Column1.timestamp", "Column1.readings"}),
#"Expanded Column1.readings" = Table.ExpandListColumn(#"Expanded Column1", "Column1.readings"),
#"Expanded Column1.readings1" = Table.ExpandRecordColumn(#"Expanded Column1.readings", "Column1.readings", {"station_id", "value"}, {"Column1.readings.station_id", "Column1.readings.value"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column1.readings1", each ([Column1.readings.station_id] = "S115")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","S115","Tuas South Avenue 3",Replacer.ReplaceText,{"Column1.readings.station_id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1.readings.value", type number}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1.timestamp", Splitter.SplitTextByEachDelimiter({"T"}, QuoteStyle.Csv, false), {"Column1.timestamp.1", "Column1.timestamp.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.timestamp.1", type date}, {"Column1.timestamp.2", type time}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Column1.timestamp.2", type text}, {"Column1.timestamp.1", type text}}, "en-SG"),{"Column1.timestamp.2", "Column1.timestamp.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Date & Time"),
#"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Date & Time", type datetime}})
in
#"Changed Type2"
```

Is it possible to do this by adding more lines to the editor. If I have to cache the data instead, how can that be done as well?

[PhilipTreacy · Accepted solution]

Hi @Joliver 

Download sample PBIX file with the following code and visuals.

Is this the kind of thing you are after?

The PBIX file contains 3 queries.  Readings gets the data from the API and transforms it into usable data.  It takes the start and end dates for the readings from 2 parameters called Start_Date and End_Date.

Stations gets the station names and ID's.

Final Data merges the Station data with the Readings and does a couple of other small steps to prepare the data for presentation.

I've left a couple of columns in the table, the date and time, in case you need them for something.  If you don't you can delete them.

The visual has slicers on the date range and the station name.

Regards

Phil

[Joliver]

Thank you so much, it works like a charm.

May I also check what adjustments are needed if I were to use it for <https://api.data.gov.sg/v1/environment/rainfall?date=2021-11-01> as well, whereby each entry log is 5s apart compared to 1s apart for the previous API.

[PhilipTreacy]

Hi @Joliver 

That's the same URL?

If it's only the timestamp frequency that is changing, the code shouldn't need any adjustment.  

Regards

Phil

[PhilipTreacy]

Hi @Joliver 

Yes you could do that by creating 2 parameters to specify your start and end dates then using those to call a function that gets the data from the API.

My question for you though is, what data do you want to extract from the API?  All stations?  A single station?  All times?  Please provide an example of what your desired result is.

Regards

Phil 

[Joliver]

Thanks for the speedy response.

I want to be able to extract all stations, have them all renamed to the provided actual station name instead of the current 'S115' naming scheme as well as all the wind speed value and time&date that corresponds with the different stations. Essentially everything from the APIs specified in a user chosen timeframe as long as the server is able to churne out the data in their API urls.

Afterwards, have those data in a line chart with the wind speed values against date&time and the lines representing the different stations which can also be filtered.