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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
abhi9255
Helper II
Helper II

How to set command timeout option

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??

1 ACCEPTED SOLUTION

Hi @abhi9255,

 

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

 

ser.png

 

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

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

9 REPLIES 9
v-frfei-msft
Community Support
Community Support

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.

 

22.png

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

I am using SQL Server Database as my source

Hi @abhi9255,

 

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

 

ser.png

 

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

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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
Not applicable

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 ?

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

 

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

Anonymous
Not applicable

Hi, what if Tabular is the source?

Where you can change timeout for PowerBI?

Anonymous
Not applicable


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?

Hi @abhi9255,

 

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

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.