Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
tkboisvert
Helper II
Helper II

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 ) :
 
 
 

GrfeZLj

 
 
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"
1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

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

v-juanli-msft
Community Support
Community Support

Hi @tkboisvert 

I can't find more solutions.

May @ImkeF  take some time to analyze this problem?

 

Best Regards

Maggie

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi @tkboisvert ,

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

 

Binary.Buffer(Web.Contents(...

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

mattio
Frequent Visitor

this worked for me for the same problem

Thanks Imke, it worked for me!

Will do! I'll report back.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors