Import data from API with dynamic URL in existing table

[tjanssen]

Hello everyone, 

I'm have a question related to PowerBi and importing data from an API. The url of the api is dynamic and looks like:
https://webapi.nl/api/1/measurements/ID1/ID2/YEAR(2020)/MONTH(11)/DAY(1)
https://webapi.nl/api/1/measurements/ID1/ID2/YEAR(2020)/MONTH(12)/DAY(2) (for a diferrent month and ...

The result is always the same, only the data is from a different month and day. It looks like:
id: 4445
date/time: 20-11-2020 11:00:00
value: 5

id: 4446
date/time: 21-12-2020 12:00:00
value: 3

If I call this API's via the 'import data' > 'web' function, then I am unable to import the data in a table with columns which already exist (with the data from the previous month(s). My wish is that I call this API each day to get the data from the previous day. Import it in one table and use that table to make diagrams etc.

It would be nice if it is possible to automatically call this API once a day or something so the data is always up to date. But that is the second step. 

Thanks in advance!

Tim

[mahoneypat · Accepted solution]

It would be easier to turn your list of dates into a table, and then format those dates in the needed string you can append to your url inside the Web.Contents on each row.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
Source = List.Dates(#date(2020, 1, 1), Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-(#date(2020, 1, 1)))), #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "DateText", each Date.ToText([Column1], "yyyy/M/d"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each "url/"&[DateText])
in
#"Added Custom1"

Regards,

Pat

[tjanssen]

Hi @mahoneypat,

Thanks for your suggestion. I tried it out and it looks really helpfull. My next question is how can I get a good list.transform statement?

With your code I have a table looking like this:
DateText:         Custom:
2020-1-1         https://webapi.meetdata.nl/api/1/measurements/ID1/ID1/2020/1/1
2020-1-2         https://webapi.meetdata.nl/api/1/measurements/ID1/ID1/2020/1/2

For each date in the DateText column it should take the API link in the column behind. I tried some small things out with list.transform and the each statement. But I don't now how to call for each date the corresponding URL.

Thanks in advance.

Tim Janssen

[mahoneypat]

You don't need List.Transform anymore.  If I understand correctly, in a new column, just wrap your Custom column expression with Web.Contents. 

= Web.Contents([Custom])

Or you can just add Web.Contents to your existing M code for the custom column.

Regards,

Pat

[Jimmy801]

Hello @tjanssen 

you can create a list of dates that is created dynamically, meaning from a specific date to today (List.Dates). 

Based on a list you can use List.Transform to iterate through al dates and call your api on every item. so you List.Transfrom could look like List.Transform(YourDatesList, each Web.Contents("https://webapi.nl/api/1/measurements/ID1/ID2/" & YourDateTransformedInAWayThatFitsYourAPI))

After that you will have a list of results that you can combine (or records or tables.. o JSON - depending on your API)

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[tjanssen]

Hello @Jimmy801 

I've tried somethings out and have the following code now:

List.Transform(List.Dates(#date(2020, 1, 1), Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-(#date(2020, 1, 1)))), #duration(1, 0, 0, 0)), each Web.Contents("https://webapi.meetdata.nl/api/1/measurements/ID1/ID1/2020/12/10"))

The List.Dates is making sure each day from the start of 1-1-2020 will be matched. My question now is, how do I get the active date in the List.Date in the URL of the API?
What I want is that each time the date in the List.Dates changed the API url changes as well like:
List.Dates(1-1-2020) -> API URL: https://webapi.meetdata.nl/api/1/measurements/ID1/ID1/2020/1/1
List.Dates(2-1-2020) -> API URL: https://webapi.meetdata.nl/api/1/measurements/ID1/ID1/2020/1/2
etc

Is there a List.Dates variable or something which I can use in the API?

Thanks for your response in advance.

Tim

[tjanssen]

Hi @Jimmy801 

Thanks for your answer. It make sense. I will try it out this or next week and will let you now of it works. 

Tim