Call a REST API multiple times using Power Query

[virtualdelo]

Hi everybody,
i have a Rest Api call that have the limited of 31 days for call.

This is the advanced editor:

let
body = "{""from"":""2021-10-01T00:00:00Z"",""to"":""2021-10-31T23:59:59Z""}",
Data= Web.Contents("***"),
DataRecord = Json.Document(Data)
in
DataRecord

1) How to create all the data history? (i have to do many call for many months that i need)
SOLUTION in this video: https://www.youtube.com/watch?v=g5bo_UAQjIE
The solution is to create a table with all the string that i have to passed like parameter (the different date).
So when i go to expand the table i'll find all the value.

2) How to update day by day only the rows modify on the field modify_date?
UPDATE i create a new post specific for the incremental Refresh.

Thanks for the support

[mattww · Accepted solution]

Hi @virtualdelo 

I work with something similar, without getting into tons of detail, here's some pointers to get you started and let me know if anything doesn't make sense or you want me to go through and part in more detail

If I've understood you right, I think it would be best to walk back from todays date (or a predefined date if you prefer) in increments.

1) Convert your code above into a function, where you can pass the start and end dates

Excuse that the escaped quotes might not be quite right

(startDate as text, endDate as text) =>

let
body = "{""from"":""" & startDate &""",""to"":""" & endDate & """"}",
Data= Web.Contents("***"),
DataRecord = Json.Document(Data)
in
DataRecord

2) Use a numbers table to generate a list of Start and End dates as required, invoke the above function

let
// Getting results for the last 365 days, in increments of 30 days
Source = List.Numbers(0, 365,30),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "EndDate", each Date.AddDays(Date.From(DateTime.LocalNow()),[Column1]*-1)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "StartDate", each Date.AddDays([EndDate],-29)),
#"Invoked Custom Function" = Table.AddColumn(#"Added Custom1", "fnGetData", each fnGetData([StartDate], [EndDate]))
in
#"Invoked Custom Function"

3) Expand the results and get rid of any unneccesary columns

Hope that helps, let me know if you need any more info

Matt

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[virtualdelo]

Thank you very much. It's correct. Is the solution that i used. The point 1 is ok. Any suggest for the point 2? Thank you so much!

[mattww]

Great, I'm just struggling with the same thing myself regarding incremental refresh.

When you set up Incremental Refresh, it does create two parameters RangeStart and RangeEnd, however when I've put these into the API call, it doesn't seem to work.

Sorry I can't be of more help on that one, although I suspect what we're looking for doesn't exist natively in Power BI