10

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.

J Henkinson
  • 133
  • 1
  • 1
  • 8

1 Answers1

14

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)])
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Thank you Alexis, great idea. And thanks for pointing out the code equivalent of the connection setting, that's really helpful. I'll try it out. But I'm worried that it may not be the cure for the the hanging issue that I experienced, because following your suggestion, I've done a bit of digging into Microsoft's Powerquery documentation and found that the CommandTimeout argument itself already has a default value of 10 minutes built into it, so even if we didn't specify that argument, the query should have terminated itself. But that wasn't the case. It ran for hours. – J Henkinson Aug 09 '18 at 01:41
  • Do you know how to do this with a local source folder. For example, my advanced editor show this as the source line Source = Folder.Files("URL"), can you please tell me how to apply a timeout on this? – Janzaib M Baloch Aug 03 '21 at 21:58
  • 1
    @JanzaibMasood There doesn't appear to be an analogous option for `Folder.Files` as far as I can tell. – Alexis Olson Aug 03 '21 at 23:40
  • @AlexisOlson, do you think there is any other way to increase the timeout for my publishing process? I have been troubleshooting this for many days, the size of my pbix is 1.5GB. When I try to publish, I get the read, write and existing connection errors. There are no errors in my dataset or pbix and also I am using the latest version of PowerBi Desktop. I have looked for all the resources online and I am about to give up. – Janzaib M Baloch Aug 04 '21 at 00:40
  • 1
    One thing I’ve done before is to pre-aggregate all the data into one file (use the same query in Excel, load to table, save the xlsx) and the connect the pbix to the xlsx. I’m not sure if this will help your case or not. You should probably post a separate question including what error messages you are seeing. – Alexis Olson Aug 04 '21 at 00:58
  • Great answer, I'm testing with ´= Web.Page(Web.Contents("URL", [Timeout=#duration(0,0,0,5)]))´ but after 5 seconds the query is not failing. Have you ever run into this issue @AlexisOlson ? – Francesco Mantovani Nov 15 '22 at 15:00