cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
_TA_
New Member

Incremental refresh and parameterize an API data source

Hi,

I'm having some difficulties configuring an API as data source with incremental refresh and the RangeStart and RangeEnd parameters in the API URL.

Originally the API has 2 hard coded variables, a start date and an end date. When I try to configure the URL dynamically by using the stated parameters in the query I get a syntax error. This is how the query looked origanally (Just to be clear, it does work like this):

= Excel.Workbook(Web.Contents(https://******.***/**/***/***?start_date=2022-02-01&end_date=2022-06-20, [Headers=[#"client-id"="******", #"client-secret"="*****"]]), null, true)

And this is what I'm trying to do now but doesnt work:

= Excel.Workbook(Web.Contents(https://******.***/**/***/***,

[Query=[start_date=DateTime.ToText(RangeStart), end_date=DateTime.ToText(RangeEnd)],

[Headers=[#"client-id"="*****", #"client-secret"="******"]]), null, true)

 

 

I have already set up incremental refresh policy.

 

If anyone could help it would be much appreciated!

1 ACCEPTED SOLUTION
Vijay_A_Verma
Community Champion
Community Champion

Change DateTime.ToText part for both start and end like thss

[start_date=DateTime.ToText(RangeStart,"yyyy-MM-dd"), end_date=DateTime.ToText(RangeEnd,"yyyy-MM-dd")]

View solution in original post

4 REPLIES 4
Vijay_A_Verma
Community Champion
Community Champion

Change DateTime.ToText part for both start and end like thss

[start_date=DateTime.ToText(RangeStart,"yyyy-MM-dd"), end_date=DateTime.ToText(RangeEnd,"yyyy-MM-dd")]

Thank you for your quick reply. It does work when I code it in the URL like this:

 

= Excel.Workbook(Web.Contents(https://*********?start_date=&DateTime.ToText(RangeStart,"yyyy-MM-dd")&"&end_date="&DateTime.ToText(RangeEnd,"yyyy-MM-dd"), [Headers=blablabla...

However if I stick with the below format where I take the query out the URL, I get an expression error. Either "3 arguments, between 1 and 2 expected " or "invalid id":

Excel.Workbook(Web.Contents(https://***********************,

[Query=[start_date=DateTime.ToText(RangeStart,"yyyy-MM-dd"), end_date=DateTime.ToText(RangeEnd,"yyyy-MM-dd")]],

[Headers=[#"client-id"="******", #"client-secret"="*********"]]), null, true)

 

 

Vijay_A_Verma
Community Champion
Community Champion

Does second construct work when you put constant date rather than variable date? 

Nope, doesn't work either. For now I'll go with the construct that does work. If you happen to have a clue about what it could be you're welcome.

 

Thanks for the date text part!

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors