Introduction
Power BI and Power Query are powerful tools for data analysis and visualization, allowing users to connect to various data sources, transform and clean data, and create insightful reports and dashboards. One critical aspect of these tools is the ability to set a command timeout, which determines how long the engine will wait for a data query to complete before canceling it.
In this article, we will explore the significance of command timeout in Power BI and Power Query, its impact on performance, and how to optimize its usage.
Importance of Command Timeout in Power BI and Power Query
In scenarios where data sources are slow to respond, queries can take longer to execute, potentially leading to performance issues. Without a command timeout, a slow-running query could potentially tie up system resources indefinitely, causing the report or analysis to become unresponsive or even crash. Therefore, the command timeout feature acts as a safety net, preventing queries from running indefinitely and protecting system stability.
By default the command timeout is set to 10 minutes and thus slow queries might hit this threshold and return the following error message.
IMPORTANT: When initially setting the command timeout, start with a conservative value that gives the query sufficient time to complete under normal circumstances. Gradually decrease the timeout if necessary, but always monitor the impact on performance.
To set the command timeout, proceed as follows:
- Open Power Query and then go to Data source settings
- Click on Change source
- Click on Advanced options
- For long running queries set it to 20 or 30 minutes. Please note, the system will accept a max value of 120 (2 hours) but please consider the impact on other users before setting this parameter to a value higher than the default (10 minutes).
- Click OK, apply changes and refresh the query again.