Forum Discussion

tkboisvert's avatar
tkboisvert
Helper II
6 years ago
Solved

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

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"
Need Help
Tips and Tricks

9 Replies