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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jsquaredz
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
ankitpatira
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

16 REPLIES 16
pthapa
Post Patron
Post Patron

Hello all,

I am also having timeout expired issue on my power bi connection. Not sure what the cause is. I tried to place the timeout duration but still didn't work for me. There is something I am missing that I don't know.

Can someone please guide me. I am running a stroed procedure from sql server.

Thank you

pthapa_0-1706734793175.png

 

 

ankitpatira
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

Source = Folder.Files("path/to/data"),

 

Do you know how to apply the timeout on this above M Code? I am trying to read from a local folder which is larger than 1GB and I am on the PPU liscense and workspace.

https://docs.microsoft.com/en-us/powerquery-m/folder-files

 

This function also takes options as a record, so I'd try the same way as the ODBC connector.

 

Namely, 

Source = Folder.Files("Path/to/folder", [timeout=#duration(0,0,30,0)]),

 

The options that the function Folder.Files takes are not spelled out, so you'll have to try a few of them to see if one of them works.

@andyparkerson , thank you for the reply. So this one you are suggesting, I tried it and PowerBi won't recognize it. What other options can I try? Is there a place where I can find their list?

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

 

Do you know how to make the Timeout for work this?

 

Source = Folder.Files("path/to/folder"),

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.