Power query table not updating when refreshed. | Excel forum

Hello Ashin,

Thank you for your question. It sounds like the issue you're facing is due to Power Query caching the old data. When you delete and replace data in the original tables, Power Query does not automatically refresh to pick up the new data until you explicitly refresh the query. Here’s how you could fix this issue and ensure Power Query pulls in the updated data:

Steps to Refresh Power Query Data:

Manually Refresh the Query:

After you paste new data into the tables, you need to manually refresh the query to update the results.
Go to the Data tab in Excel.
Click on Refresh All (or click on the specific query and select Refresh).
This will reload the new data from the tables into the query and update the output based on the new data set.

Check the Query Source:

Make sure your query is still pointing to the correct tables (i.e., the ones you pasted the new data into).
Open Power Query Editor by clicking Data > Queries & Connections, right-click on the query, and choose Edit.
In Power Query Editor, check the Source step to confirm it's still linked to the correct tables.

Clear the Cache:

Sometimes Power Query caches data, and a simple refresh might not fix the issue. You can force the cache to clear by:
Going to the Data tab.
Selecting Queries & Connections.
Right-clicking on your query and selecting Clear Cache.

Check for Hardcoded Data:

Ensure there’s no hardcoded data in the query steps. In Power Query, sometimes hardcoded values from previews may be retained in the transformation steps (especially if certain filters or transformations were applied). In the Power Query Editor, walk through the steps to see if any of the transformations are based on the old row count or old data.

Check for Pivot Tables or Calculations:

If the query data feeds into Pivot Tables or other calculations, make sure those are also refreshed after the data update. Pivot Tables, in particular, require manual refreshes.
Automation: Set Up Auto-Refresh

If you don’t want to refresh manually each time, you can set up Power Query to refresh automatically whenever the file is opened:
Go to Data > Queries & Connections.
Right-click on the query and choose Properties.
In the dialog box that opens, check the box for Refresh data when opening the file.
This way, every time you open the Excel file, it will pull the latest data automatically.

Troubleshooting Checklist:
Make sure you’re replacing data in the same table names.
Ensure the query connections are set to refresh automatically or refresh them manually after updating the source tables.
Verify there are no filters or steps within the query editor that are limiting the results to the old data set.
By following these steps, Power Query should hopefully update and reflect the new data.

I hope this helps.

Kind regards
Marius Barnard
STL