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
Helper V
Helper V

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors