-
-
Save CurtHagenlocher/68ac18caa0a17667c805 to your computer and use it in GitHub Desktop.
let | |
Value.WaitFor = (producer as function, interval as function, optional count as number) as any => | |
let | |
list = List.Generate( | |
() => {0, null}, | |
(state) => state{0} <> null and (count = null or state{0} < count), | |
(state) => if state{1} <> null | |
then {null, state{1}} | |
else {1 + state{0}, Function.InvokeAfter(() => producer(state{0}), interval(state{0}))}, | |
(state) => state{1}) | |
in | |
List.Last(list), | |
Web.ContentsCustomRetry = (url as text, optional options as record) => Value.WaitFor( | |
(i) => | |
let | |
options2 = if options = null then [] else options, | |
options3 = if i=0 then options2 else options2 & [IsRetry=true], | |
result = Web.Contents(url, options3 & [ManualStatusHandling={429}]), | |
buffered = Binary.Buffer(result), /* avoid risk of double request */ | |
status = if buffered = null then 0 else Value.Metadata(result)[Response.Status], | |
actualResult = if status = 429 then null else buffered | |
in | |
actualResult, | |
(i) => #duration(0, 0, 0, i*0.1)) | |
in | |
Web.ContentsCustomRetry("http://www.bing.com") |
Hi CurtHagenlocher,
First of all thanks for sharing the code. I applied it as a function and then applied it to my code instead of Web.Contents as you can see in the prints in the links below that I send as an attachment. Ok it worked I can load my data but when I upload to the PowerBi service I run into the following error "Query contains unsupported function. Function name: Web.Contents".Sorry the annoying but you would know what could be causing this error.
Looking for a solution I found articles like this ( https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/ ) in which they suggested applying RelativePath so I applied it in your code. But I was also unsuccessful.
I thank you for your attention.
The Power BI service works only when a query is statically analyzable for data sources. The expression '(url) => Web.Contents(url)' is not currently analyzable for which URL is being accessed while the expression '() => Web.Contents("https://www.bing.com")' is. So to use this in a normal query and have it work in the Power BI service, you'll need to move the actual text literal containing the URL into the function.
I'm facing the same problem, I use your function to refresh manually my data and adapt to y script but now I want to refresh my Power BI in the Power BI service online but I didn't succeed, the service says that I'm using dynamic web sources so that it can't be refresh by PowerBI service. I tried to us the RelativePath function but it didn't succeed:
`let
Source = (string1 as text) =>
let
//Function to wait
Value.WaitFor = (producer as function, interval as function, optional count as number) as any =>
let
list = List.Generate(
() => {0, null},
(state) => state{0} <> null and (count = null or state{0} < count),
(state) => if state{1} <> null
then {null, state{1}}
else {1 + state{0}, Function.InvokeAfter(() => producer(state{0}), interval(state{0}))},
(state) => state{1})
in
List.Last(list),
//Function to Call API
Web.ContentsCustomRetry = (url as text, optional options as record) => Value.WaitFor(
(i) =>
let
options2 = if options = null then [] else options,
options3 = if i=0 then options2 else options2 & [IsRetry=true],
result = Web.Contents(url, options3 & [ManualStatusHandling={429}]),
buffered = Binary.Buffer(result), /* avoid risk of double request /
status = if buffered = null then 0 else Value.Metadata(result)[Response.Status],
actualResult = if status = 429 then null else buffered
in
actualResult,
(i) => #duration(0, 0, 0, i0.1)),
//Actual Execution Starts Here
url = "https://sandbox-oauth.piste.gouv.fr",
url2="api/oauth/token",
body = [
client_id = "xxxxxxxx",
client_secret = "xxxxxxxx",
audience = "openid",
grant_type = "client_credentials"
],
GetJson = Json.Document(
Web.Contents(
url,
[RelativePath= url2, Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(Uri.BuildQueryString(body))]
)
),
AccessTokenHeader = GetJson[token_type] & " " & GetJson[access_token],
corps = Json.FromValue([cid= string1]),
myRaw = Web.ContentsCustomRetry(
"https://sandbox-api.piste.gouv.fr", [RelativePath = "dila/legifrance-beta/lf-engine-app/consult/getSectionByCid", Headers=[Authorization=AccessTokenHeader,#"Content-Type"="application/json"], Content=corps]),
//actualMeta = Text.From(GetMetadata[Response.Status]),
//Final function to create response string
GetConsloidatedTargets = () =>
let
FinalContent = Json.Document(myRaw)
in
FinalContent,
finaldata = GetConsloidatedTargets()
in
finaldata
in
Source`
If you can maybe find a solution to my problem it will be very helpful.
Thank you per advance
Judging by that response the service is sending a 522 when you're being throttled, so I imagine you ought to be able to change step "actualResult" of Web.ContentsCustomRetry to be