Power Query Problems

Why Excel Power Query API Calls Run More Than Once and How to Fix It

Tauseeq MagsiTauseeq Magsi Nov 10, 2026 869 views

Question details

The user wants to understand why Power Query makes multiple API calls during data loads or refreshes and how to prevent exceeding external API rate limits.

Why Excel Power Query API Calls Run More Than Once
Product
Microsoft Excel
Device & OS
not provided
Scenario
Loading or refreshing a query that pulls and transforms data from an external API.
Observed behavior
The API is called multiple times because Power Query re-evaluates the data connection upon loading and refreshing, instead of using the cached preview data.
Before you start

Review your API provider's rate limits and documentation to understand how many requests are allowed per minute or hour before making structural changes to your Power Query setup.

Solution 1Recommended

Export Data to a Staging Table

The most reliable way to prevent excessive API calls is to save the raw API response to a permanent location in Excel before applying complex transformations.

Power Query is a data transformation engine, not a storage system. It evaluates queries upon every load and refresh. To bypass this and protect your API limits, you should retrieve the data once, store it in your workbook, and then run your transformations on the stored data.

1
Connect and download raw data

Connect to your API using Power Query and download the raw data without applying any heavy transformations or expanding complex records.

2
Load to a worksheet

Click 'Close & Load To...' and load this initial query directly into a dedicated Excel worksheet. This will act as your staging table.

3
Create a secondary query

Select the newly created staging table in your worksheet, go to the 'Data' tab, and click 'From Table/Range' to create a new query.

4
Apply transformations locally

Apply all your complex formatting, expansions, and transformations to this second query. It will now process locally using the worksheet data without hitting the external API again.

Export Data to a Staging Table
Free Microsoft Office alternative

Handle Your Data Efficiently with WPS Office

While Power Query's API handling requires complex workarounds in Microsoft Excel, you can handle large datasets, complex formulas, and data analysis efficiently using WPS Spreadsheet. It provides a lightweight, highly compatible, and free alternative to Microsoft Office.

  1. 1. Download and Install: Visit the official WPS website to download the free installation package for your operating system.
  2. 2. Open Your Spreadsheets: Launch WPS Spreadsheet and easily open your existing Excel workbooks and data files.
  3. 3. Analyze Data: Utilize powerful built-in functions, pivot tables, and charts for seamless data analysis.
Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats without data loss.Lightweight installation with fast startup and smooth processing of large data tables.Familiar user interface makes migrating from Microsoft Excel seamless and intuitive.Built-in advanced data analysis, pivot tables, and visualization tools.
microsoft office alternative - wps office

Frequently Asked Questions

Does generating a preview in the Power Query Editor count as an API call?

Yes, generating the data preview in the Power Query Editor triggers an API call. When you subsequently click 'Close & Load' to send the data to a worksheet, another API call is made because the preview data is not saved as permanent storage.

Can I schedule a refresh to only happen once a day to save API limits?

Yes. You can manage how often the API is queried by going to 'Data' > 'Queries & Connections', right-clicking your query, selecting 'Properties', and configuring the refresh frequency under the 'Usage' tab.

Why doesn't Table.Buffer stop API calls completely across sessions?

Table.Buffer only stores the data in your computer's RAM during a single query execution run. Once the refresh completes, the memory is cleared. The next time you click 'Refresh', the API must be called again to fetch fresh data.

How do I stop Power Query from refreshing automatically in the background?

Go to the query properties by right-clicking the query in the 'Queries & Connections' pane, select 'Properties', and uncheck 'Enable background refresh'. This allows you to manually control exactly when the query evaluates.