Why Excel Power Query API Calls Run More Than Once and How to Fix It
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.

- 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.
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.
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.
Connect to your API using Power Query and download the raw data without applying any heavy transformations or expanding complex records.
Click 'Close & Load To...' and load this initial query directly into a dedicated Excel worksheet. This will act as your staging table.
Select the newly created staging table in your worksheet, go to the 'Data' tab, and click 'From Table/Range' to create a new query.
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.

Apply Table.Buffer to Prevent Mid-Query Re-evaluations
Use the Table.Buffer function to load a table into memory during query execution, preventing Power Query from re-calling the API for subsequent transformation steps.
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. Download and Install: Visit the official WPS website to download the free installation package for your operating system.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheet and easily open your existing Excel workbooks and data files.
- 3. Analyze Data: Utilize powerful built-in functions, pivot tables, and charts for seamless data analysis.

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.




