Troubleshooting Power Query “Timeout” Errors on Large SQL Datasets

A Power Query SQL timeout error, such as Microsoft SQL: Execution Timeout Expired or DataSource.Error: The query has timed out, indicates that a query took longer to run than the allowed execution threshold. This does not mean your network is disconnected or your database is permanently broken. It means the database engine or the local mashup engine reached its time limit before the data could be fully returned, usually due to unindexed queries, broken query folding, or unoptimized command timeout defaults.

Fast Fix

Open Power Query Editor, select the Source step, and open the Advanced Editor. Inside the connection string function (e.g., Sql.Database), add [CommandTimeout=#duration(0, 0, 30, 0)] to extend the execution limit to 30 minutes. If the query still times out, verify that Query Folding is active or write a native SQL query with an indexed WHERE clause to filter the dataset at the server.

---

Diagnostic Snapshot

Property	Details
Typical Error Strings	Execution Timeout Expired, The timeout period elapsed prior to completion of the operation, DataSource.Error: The operation has timed out
Default Command Timeout	Often defaults to 10 minutes (or 600 seconds) in Power Query if unspecified, or driver defaults (30–60 seconds).
Connection vs. Command	Connection Timeout: Server failed to respond to the initial handshake.<br>Command Timeout: Server responded, but the query took too long to complete.
Primary Root Causes	Missing database indexes, Query Folding breakdown, table locking/blocking on SQL Server, or pulling millions of rows over a VPN.

---

Why Large SQL Queries Time Out in Power Query

Power Query uses two distinct timeouts when interacting with external databases:

1. Connection Timeout: The time allowed to reach the server, authenticate, and establish a session (usually 15–30 seconds).
2. Command Timeout: The time allowed for the database engine to compile, execute, and deliver the resulting dataset back to Power Query.

When querying large datasets, the command timeout is usually what triggers the error.

By default, if no command timeout is declared in the M-code step, Power Query relies on the default driver settings. If your SQL Server requires 12 minutes to scan a 50-million-row table and the timeout is set to 10 minutes, the query is killed at minute 10.

However, increasing the timeout is often only a temporary workaround. If the query previously ran in seconds but suddenly times out, the real problem is usually broken Query Folding or server-side blocking.

---

Step-by-Step Troubleshooting

Step 1: Extend the Command Timeout in M-Code

If the query simply needs more time to complete legitimate heavy processing, set an explicit timeout window.

1. In Excel, go to Data > Queries & Connections and double-click the failing query.
2. In the Applied Steps pane, select the Source step.
3. In the formula bar, locate your database call. It will look like this: = Sql.Database("sqlserver.company.com", "DataWarehouse")
4. Add the CommandTimeout parameter using the M #duration format: #duration(days, hours, minutes, seconds).(This example extends the threshold to 45 minutes.) = Sql.Database("sqlserver.company.com", "DataWarehouse", [CommandTimeout=#duration(0, 0, 45, 0)])
5. Press Enter and click Refresh Preview.

Sql.Database("Server", "Database", [CommandTimeout = #duration(0, 0, 30, 0)])
                                   └──────────────┬─────────────────────────┘
                                   Days, Hours, Minutes, Seconds

---

Step 2: Check for Broken Query Folding

Query Folding is the mechanism where Power Query translates your transformation steps (such as filters, joins, and group-bys) into native SQL statements and lets the database server process them.

If a step breaks folding, Power Query stops sending SQL to the server. Instead, it requests the entire raw table across the network and attempts to process the filtering in your computer’s local RAM. This causes immediate timeouts on large tables.

How to verify Query Folding:

1. In the Applied Steps list, right-click each step from the bottom upward.
2. Check if the option View Native Query is clickable:
   • Clickable (Active): The database is executing the step.
   • Grayed Out (Broken): Query folding stopped at or immediately before this step.
3. Steps that commonly break folding include:
   • Custom M functions or Table.AddColumn with complex non-standard logic.
   • Changing types to non-primitive types.
   • Certain string or text extractions.
   • Merging with an external file or local Excel table.

The Fix: Reorder your applied steps. Move all simple filters (Date >= 2025-01-01), column selections, and inner joins to the very top of your applied steps immediately following Navigation. Only perform un-foldable transformations at the very end of the query.

For memory allocation problems that follow broken folding, consult Troubleshooting “Evaluation ran out of memory” in the Power Query Engine.

---

Step 3: Bypass the Visual Editor with a Native SQL Query

If you must retrieve aggregated data from a multi-million row table, do not load the raw table and click “Group By” in Power Query. Use a native SQL query to force the SQL Server to aggregate the data before transmitting it:

1. In Power Query, click New Source > Database > SQL Server Database.
2. Enter your Server and Database names.
3. Expand Advanced options.
4. In the SQL statement box, write an optimized query: SELECT CustomerID, SUM(TotalAmount) AS TotalSpent, COUNT(OrderID) AS OrderCount FROM dbo.Orders WITH (NOLOCK) WHERE OrderDate >= '2025-01-01' GROUP BY CustomerID
5. Specify CommandTimeout directly in the advanced options dialog box.
6. Click OK.

Note on WITH (NOLOCK): If your query times out during normal business hours because transactional updates are locking tables, querying with the read-uncommitted hint (NOLOCK) or using snapshot isolation can prevent your reporting query from getting blocked by transactional writes. Verify with your database administrator before using it.

---

Step 4: Rule Out Network and VPN Bottlenecks

A query running across a remote VPN connection behaves differently than one running on a corporate local area network (LAN).

• If your query transfers millions of rows, high network latency can cause the client connection buffer to stall, triggering a timeout.
• To check whether the delay is network-related, add a Keep First Rows (1000) step immediately after Source. If the 1,000-row preview loads instantly but a full refresh fails, the database query is fine, but the volume of data is too large for your network connection.

If large data volumes repeatedly cause client-side buffering failures, review Power Query “Buffer Overflow” when loading 1M+ Rows. If the mashup evaluation exceeds global tenant processing limits, see “An error occurred in the ‘Data’ query. Expression.Error: Evaluation exceeded the allowed time”.