How to set a max run time / timeout for Power Query?

Question

Is there a way to set a max run time or a timeout factor in Power Query so that after a specified period the query would terminate itself regardless of whether it was successfully executed or not?

This is important for me because I have built various queries at my workplace that usually run fine (as in I have been running it daily without issues for months and years) but occasionally, they would hang (likely because of clashing with another process on the server at that moment, when the server was under heavy load) and keep making read requests on the server indefinitely - One time IT have told me they logged more than 7 million reads from my machine into the database within a few hours. In cases, these have caused the servers to crash which leads to extensive downtime.

So I would like to know if there is any setting, or any thing that I can build into the query itself to ensure it terminates after a certain period of time.

I'm proficient with the M Power Query langue.

Thanks.

[Update 1] Thanks Alexis for the suggestion below regarding setting a CommandTimeout value when coding the connection. It's a great pointer.

I'll try it out, do some tests and report back if it conclusively fixed the issue or not.

But in the meantime, I've done a bit of digging into Microsoft's Power Query documentation and found that the CommandTimeout argument itself already has a default value of 10 minutes built into it, so theoretically even if we didn't specify that argument, the query should have terminated itself. But that wasn't the case. It ran for hours.

I wonder if it is a bug with Excel version of Power Query? Because I do use Power Query within Power BI as well, and over there I haven't seen it crashing and hanging yet (admittedly I've been using the Excel version more frequently than I did Power BI).

However, if anyone has any other suggestions on potential fixes for this problem, that would be much appreciated. Thanks.

Answer 1

I found a pretty decent answer to this here. Here are the steps posted on that forum:

1. Have the Power BI Desktop file open and in Report View
2. Click on the arrow for "Edit Queries" (in the External Data section)
3. A dropdown will appear - then click "Data Source Settings"
4. Data source settings pop-up window will appear
5. Click on "Change Source..."
6. Another pop-up will appear
7. Click on Advanced Options (a drop-down of sorts will appear within the pop-up)
8. "Command timeout in minutes (optional)" will be the first option
9. Enter a value - I chose to enter 60 minutes but feel free to enter any value
10. Then apply the query changes and wait till the refresh is complete

This was written for Power BI, but it works in the Excel query editor as well. In summary,

1. Open Query Editor
2. Choose File > Options and settings > Data source settings
3. Select your source and click on Change Source...
4. Expand Advanced options and enter a Command timeout value

When I tried this with a connection to a SQL server it added a CommandTimeout argument to my Source step. You can just use this code instead and skip all the clicking:

= Sql.Database("server", "DB", [CommandTimeout=#duration(0, 0, 15, 0)])

Doing it via the Data source settings may be preferable if you aren't connecting to a SQL server as the parameters might be different. E.g.

= Web.Page(Web.Contents("URL", [Timeout=#duration(0,0,15,0)]))

or

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