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
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
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
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