Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Call a REST API multiple times using Power Query

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

1 ACCEPTED SOLUTION
mattww
Responsive Resident
Responsive Resident

Hi @Anonymous 

 

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.

 

 

View solution in original post

3 REPLIES 3
mattww
Responsive Resident
Responsive Resident

Hi @Anonymous 

 

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.

 

 

Anonymous
Not applicable

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors