cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate I
Advocate I

Query Timeout on update

I am pulling in an update from a web source, but this source can take 3 minutes + to return data.  Power BI times out before the server responds.  I would like to adjust this timeout timer.  Can we make this user adjustable setting?

1 ACCEPTED SOLUTION
Community Champion
Community Champion

@jsquaredz What you need to do is in power bi desktop go to query editor then right click your web data source and click Advanced Editor. This will give you access to M code behind it. You need to specify timeout interval in there manually. See below image and then should work hopefully.

 

Capture.PNG

View solution in original post

11 REPLIES 11
Community Champion
Community Champion

@jsquaredz What you need to do is in power bi desktop go to query editor then right click your web data source and click Advanced Editor. This will give you access to M code behind it. You need to specify timeout interval in there manually. See below image and then should work hopefully.

 

Capture.PNG

View solution in original post

I have this issue in excel where I am referencing a URL in a table. How would this solution be applied as per below?

 

= Excel.Workbook(Web.Contents(
let
Source = Excel.CurrentWorkbook(),
SpmReports = Source{[Name="SpmReports"]}[Content],
#"Report URL" = SpmReports{6}[Report URL]
in
#"Report URL"
), null, true)

Hi @ankitpatira,

 

do you know if this could also work for OData-Feed?

 

Best,

J. 

Yes, it works.

you have to add a "null" in between

OData.Feed("http://some.url/service.svc/", null, [Timeout=#duration(0, 0, 5, 0)])

 

I'm having a similar problem (I think), but the solution described here doesn't seem to work.

 

Problem:

Problem.JPG

 

Attempted solution:

Solution.JPG

 

New problem:

Problem2.JPG

 

Trying to reduce argument number to 2:

Solution2.JPG

 

I have tested the ODBC connection in the Windows "ODBC Data Sources" app and it worked. Any ideas what I could be doing wrong / if there are any other workarounds?

 

Thanks!

 

Here's a reply to an old question:

 

https://docs.microsoft.com/en-us/powerquery-m/odbc-datasource

 

The second parameter is a record, so you want to include your command and connection timeouts inside the record, instead of multiple separate parameters.

 

Thus, you would have:

Source = Odbc.DataSource("dsn=PostgresSql64bit", [HierarchicalNavigation=true, ConnectionTimeout=#duration(0,0,5,0), CommandTimeout=#duration(0,0,5,0)])

The two durations don't have to match. The first is for the connection (how long it takes to initially connect), and the second is for the command (how long it takes to run the query).

hi guys!

 

I kinda have a similar issue.

I am using the logic from Harry Gates to retrieve data from Essbase via XMLA.

http://harrygates-essbase-blog.blogspot.de/2016/09/power-query-how-to-connect-to-essbase.html

 

The timeout in my script is in the properties section, set to <Timeout>90000000</Timeout>

Despite this i am still getting the timeout error.

In Excel, with SmartView, i am able to retrieve the value in an acceptable time.

 

How can i solve this? Is there in PBI another timeout section somewhere???

Cheers.

Thanks for the workaround.

 

[timeout=#duration(0,0,30,0)]))

 

Can you share these parameters?

 

Would like to see this as a setting in the query builder.

 

>>[timeout=#duration(0,0,30,0)]))

 

This is a TimeSpan like parameter, with the values representing Days, Hours, Minutes, Seconds.

 

So, this setting represents a timeout of 30 minutes.

Can you advise if its valid to go over for a given unit?

 

For instance 0,0,0,90 instead of 0,0,1,30

This seems to be fine.  I didn't directly test with the Power BI client, but I tested with the TimeSpan class in C# (I'm a developer), and it didn't complain at all.

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors