Forum Discussion
Import data from API with dynamic URL in existing table
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
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
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