Odd Web.Contents behavior making multiple calls to API based on load time

[tkboisvert]

To be clear syntactically and logically, we're not having an issue. The clarification we need is based around API calls that take a long time to serve from source. From what we can tell, when PowerQuery uses Web.Contents to get a JSON packet back, it will cache the dataset internally, if set up correctly. Meaning one call, and one load on the server. We have experienced this in production and IS the desired effect. 

However, if the API takes too long to load, PowerBI will ignore the cached dataset and send an identical query out again. This is NOT the desired effect. Same exact PowerQuery, same API ( Same data, just a larger superset ), different behavior.

The cache seems to be ignored around the 4 minute mark. If we place the same data statically, and call it ( not engaging the server for a refresh ) it works perfectly well, the cache is used. The problem only seems to occur if the second query has been waiting around for a while.

Short loading query -> one call. Long loading query -> one call for each.

Can anyone confirm this effect or provide a fix?

Below are all the settings we have en/disabled and our PowerQueries

We have PowerQuery set up with the following options in this blog: https://blog.crossjoin.co.uk/2019/10/13/why-does-power-bi-query-my-data-source-more-than-once/

• Parallel loading disabled
• Background refresh disabled
• Web.Contents has a Timeout enforced and IsRetry=false
• All privacy settings set to ignore
• Using a Fx as the base query

The Query structure looks like this ( ignore the error on the header table I've take the file out of the intranet ) :

SvcOrderFx:

() as table=>

let

    WebCall = Web.Contents("http://intranetapi.com?request=serviceorders",

        [Timeout=#duration(0, 0, 30, 0), IsRetry=false]),

    Source = Json.Document(WebCall),

    #"Converted to Table" = Record.ToTable(Source)

in

    #"Converted to Table"

ServiceOrderHeader:

let

    Source = SVCOrderFX(),

    Value = Source{0}[Value],

    #"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

    #"Converted to Table"

ServiceOrderDetail:

let

    Source = SVCOrderFX(),

    Value = Source{1}[Value],

    #"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

    #"Converted to Table"

[ImkeF · Accepted solution]

There is another method described here: Chris Webb's BI Blog: Speed Up Data Refresh Performance In Power BI Desktop Using Table.View Chris W...
that might be more reliable.

[Conjured]

Hello!

I have tried all of the mentioned in this thread solutions, but it doesn't work for me. 

If I make a new API GET request of data then PQ makes 2 API calls instead of 1.

However, after I have received the data, if I repeat exactly the same query, then PQ makes 1 API call.  

Please help, I am stuck.

[tkboisvert]

While I no longer have the environment to test this, this is the correct answer.

[v-juanli-msft]

Hi @tkboisvert 

I can't find more solutions.

May @ImkeF  take some time to analyze this problem?

Best Regards

Maggie

[ImkeF]

Hi @tkboisvert ,

don't know the reason for this either.

If no further answer is coming up here, I'd recommend to post your question in this forum instead: https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

[ImkeF]

Hi @tkboisvert ,

actually, there is a thing that you can try: Buffer the Web.Contents like so:

Binary.Buffer(Web.Contents(...

[mattio]

this worked for me for the same problem

[arnaud_l]

Thanks Imke, it worked for me!

[tkboisvert]

Will do! I'll report back.