-
-
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") |
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
actualResult = if status = 429 or status = 522 then null else buffered
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
Hi CurtHagenlocher,
Using your gist, I tried to create my own function for retrieving value from an API which has limit of 50 fetch per minute. but somehow function doesn't seems working or desired output.
While the error after trying sometime i receives is as follows:
An error occurred in the ‘’ query. DataSource.Error: Web.Contents failed to get contents from 'https://finnhub.io/api/v1/stock/price-target?symbol=SNE&token=<Get Your Own Key from Finnhub.io for free>' (522): Details: DataSourceKind=Web DataSourcePath=https://finnhub.io/api/v1/stock/price-target Url=https://finnhub.io/api/v1/stock/price-target?symbol=SNE&token=<Get Your Own Key from Finnhub.io for free>