How to set command timeout option

[abhi9255]

My query is taking more time to refresh and when i refresh im getting command timeout error .

So i need to set command timeout option in Power BI desktop but it is disabled at the moment how to enable it??

[WillBeeSEA]

I am using Dataverse and whenever I try to load (just two tables).

The data displays correctly in the Power Query Editor, but when I try to Close & Apply, it times out.

(My connection speed seems to be fine; 800+Mbps down, ~100Mbps up.)

Finding this discussion thread, I tried taking the CommandTimeout expression in the SQL solution:

,[CommandTimeout=#duration(0,0,35,0)]),

and adding it to my line, but it did not work:

I stayed with the same 35-minute setting (did not want to fiddle with days, hours or seconds at this point),

but I received an error that CommandTimeout is not valid.

Anything you can tell me would be helpful.    

Thank you.

[v-frfei-msft]

Hi @abhi9255,

What is your data source? I take a Excel data source as an example. In power bi desktop, firstly, go to the query editor, then you can edit your M code in Advanced Editor to set command timeout. See below image.

Here is the M code for your reference.

let
    Source = Excel.Workbook(File.Contents("filepath"), null, true,[timeout=#duration(0,0,30,0)]),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", Int64.Type}})
in
    #"Changed Type"

For the parameter, the values represent Days, Hours, Minutes, Seconds. So, this setting represents a timeout of 30 minutes.

Regards,

Frank

[abhi9255]

I am using SQL Server Database as my source

[v-frfei-msft · Accepted solution]

Hi @abhi9255,

For SQL database, we can change the command timeout in Advanced options of  Data source settings directly.

And here is the M code for your reference.

let
    Source = Sql.Databases("Servername", [CommandTimeout=#duration(0, 0, 35, 0)]),
    TestGatewayDocs = Source{[Name="databasename"]}[Data],
    dbo_Product3 = databasename{[Schema="dbo",Item="Product3"]}[Data]
in
    dbo_Product3

Regards,

Frank

[Anonymous]

This does not necessarily help! We are running queries against an MSSQL DB and no matter where these values are set, the flows can fail after around 15 minutes due to timeout issues. You may need to contact the DB or Gateway admin to adjust the settings on their side.

[Anonymous]

I have many queries in the power i report, and i have also set up command timeout, But still when i try to refresh it fails with MSSql timeout error for one particular table.

Also how do you decide how much  Command timeout should be ?

[nasheno]

asazure://westus.asazure.windows.net/ucmbusinessanalyticscube

My source is the above from analysis services. How do I change the timeout code there?

[Anonymous]

Hi, what if Tabular is the source?

Where you can change timeout for PowerBI?

[Anonymous]

Hi, @v-frfei-msft 

I keep getting scheduled refresh fails due to
Before the data import for (Data Source Name) finished, its data source timed out.

Why is there no option like this for PBI desktop for other sources like excel?

[v-frfei-msft]

Hi @abhi9255,

Does that make sense, If so, kindly mark my answer as a solution to close the case please.

Regards,

Frank