Forum Discussion

abhi9255's avatar
abhi9255
Advocate II
8 years ago
Solved

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 enab...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    8 years ago

    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

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
8 years ago

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's avatar
abhi9255
Advocate II
8 years ago

I am using SQL Server Database as my source

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    8 years ago

    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

     

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi abhi9255,

       

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

       

      Regards,

      Frank

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago


      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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi, what if Tabular is the source?

      Where you can change timeout for PowerBI?