Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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")]
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)
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.