Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Demonstrates the use of a custom retry duration with Web.Contents.
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")
@vishalniit

This comment has been minimized.

Copy link

@vishalniit vishalniit commented Aug 12, 2020

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.

(params) =>
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, i*0.1)),
		//Function to retrieve value from Data
		StatusCode = (Status, RawData, Symbol) => 
		let
			RAWN = (data,field) =>
				let
					result= Text.From(Record.Field(data, field))    
				in
					result,
			Map = #table({"HTTPCODE", "CODE"}, {
				{"404", Text.Combine({"{""symbol"":""",Symbol,""",""updatedDate"":""2020-03-12"",""priceTargetAverage"":0,""priceTargetHigh"":0,""priceTargetLow"":0,""numberOfAnalysts"":0,""currency"":""USD""}"})},
				{"429", Text.Combine({"{""symbol"":""",Symbol,""",""updatedDate"":""2020-03-12"",""priceTargetAverage"":0,""priceTargetHigh"":0,""priceTargetLow"":0,""numberOfAnalysts"":0,""currency"":""USD""}"})},
				{"200", Text.Combine({"{""symbol"":""",Symbol,""",""updatedDate"":""",Date.ToText(DateTime.Date((DateTime.LocalNow())),"yyyy-MM-dd"),""",""priceTargetAverage"":",RAWN(RawData,"targetMedian"),",""priceTargetHigh"":",RAWN(RawData,"targetHigh"),",""priceTargetLow"":",RAWN(RawData,"targetLow"),",""numberOfAnalysts"":0,""currency"":""USD""}"})}
				}
				)			
		in
			Map{[HTTPCODE = Status]}[CODE],
	//Actual Execution Starts Here		
	apiKey = "<Get Your Own Key from Finnhub.io for free>",
    URL = Text.Combine({"https://finnhub.io/api/v1/stock/price-target?symbol=",params,"&token=",apiKey}),
	myRaw = Web.ContentsCustomRetry(URL),      
    //actualMeta = Text.From(GetMetadata[Response.Status]),
		//Final function to create response string
		GetConsloidatedTargets = () =>
		let  
			FinalContent = Json.Document(StatusCode("200",Json.Document(myRaw), params)),
			PriceTargetsData= Text.Combine({Text.From(Record.Field(FinalContent, "priceTargetHigh")), Text.From(Record.Field(FinalContent, "priceTargetLow")), Text.From(Record.Field(FinalContent, "priceTargetAverage")),Record.Field(FinalContent, "updatedDate")},":")
		in
			PriceTargetsData,
	finaldata = GetConsloidatedTargets()
in
    finaldata

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>

@CurtHagenlocher

This comment has been minimized.

Copy link
Owner Author

@CurtHagenlocher CurtHagenlocher commented Aug 14, 2020

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
@vinicusdiass

This comment has been minimized.

Copy link

@vinicusdiass vinicusdiass commented Sep 17, 2020

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.

https://prnt.sc/uilohh
https://prnt.sc/uiloxn

@CurtHagenlocher

This comment has been minimized.

Copy link
Owner Author

@CurtHagenlocher CurtHagenlocher commented Sep 28, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.