Incremental refresh and parameterize an API data source

[Anonymous]

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!

[Vijay_A_Verma · Accepted solution]

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")]

[Anonymous]

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]

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

[Anonymous]

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!