Dynamic Web.Contents Power BI Refresh Error

[aTChris]

Im having issues refreshing my data with Web.Contents

I need to pull data from an API which limits the results to 1000. I found the amazing function to invoke a custom function to construct the URI with the correct page parameters.

(page as text) =>
let

Source = Json.Document(Web.Contents("https://xx.xxx.com/xx/xx/xx?size=1000&page=" & page)),

I then found the schedule refresh fails due to the URI check. It appears the skip check is not supported in the cloud yet. Im having trouble with the syntax of a workaround.

(page as text) =>
let

Source = Json.Document(Web.Contents(
"https://xx.xxx.com/xx/xx/xx?size=1000&page=1",
[Query=[page= & page]])),

Im getting the error token literal expected.

Its 10pm my brain isnt working. What am I missing.

Thanks

[primolee]

Hello, thank you so much for raising this question, the solution to this question is a great help.

I have found another problem on Sharepoint credential when using relative path in Web.Contents.  Following link is my thread.

https://community.powerbi.com/t5/Service/Web-Contents-with-dynamic-file-URL/td-p/928233

Could anyone please give me a hand on this?  Thank you so much!

[v-juanli-msft · Accepted solution]

Hi @aTChris 

there are several blog posts on how to work around this problem.

Web.Contents(), M Functions And Dataset Refresh Errors In Power BI

Dynamic Web.Contents() and Power BI Refresh Errors

Using The “Skip Test Connection” Option For Power BI Data Sources To Avoid Scheduled Refresh Failure...

As for the syntax of the workaround, 

you could check this blog to learn more.

Revisiting Dynamic Web.Contents()

Based on my understanding, your code can be modified as below

(pagenumber as text) =>
let

Source = Json.Document(Web.Contents(
"https://xx.xxx.com/xx/xx/xx?size=1000&page=1",
[Query=[page=pagenumber]])),

You can create a query parameter called "pagenumber",

Power BI Desktop Query Parameters, Part 1

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aTChris]

@v-juanli-msft  Thank you, the script still doesn't seem to work in the Cloud. Im waiting for the skip test option to be added. Do you know when that is scheduled for release?

[blopez11]

Maybe try something like the below

(pPage as text) =>
let
gBaseUrl = "https://xx.xxx.com/xx/xx/xx",
gMaxSize = 1000,

vOptions = [Query=[size=Text.From(gMaxSize), page=pPage]],
Source = Json.Document( Web.Contents ( gBaseUrl, vOptions ) ),

...