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

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

     

11 Replies

  • 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

         

  • WillBeeSEA's avatar
    WillBeeSEA
    Helper III
    1 year ago

    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.

  • jessezimm1998's avatar
    jessezimm1998
    Frequent Visitor
    1 year ago

    Any ideas for setting command timeout for exchange contents function?