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