Power Query Refresh Not Updating Table Data in Excel

Question

I'm experiencing an issue with Power Query refresh in Excel. I'm using Excel Microsoft Office Home and Business 2016 and the issue started after the May 30th, 2024 update.

Here's a detailed description of the problem:

• I upload data from various sources using Power Query.
• When I refresh the data, everything refreshes at the Power Query level (I see the new information in Power Query Editor).
• There are no error messages displayed during the refresh process.
• However, the new information isn't reflected in the final table in the Excel worksheet (new data is not loading to the table).
• I need to manually manipulate table properties and refresh again for the new data to appear.
  To get the new data to appear, I typically need to manually modify the external data properties in the table.
• Specifically, I uncheck the boxes for "Insert cells for new data, clear unused cells," and then select "Insert entire rows for new data, clear unused cells".

After making these changes and refreshing again, the new data appears in the table.

I've noticed that manually modifying the external data properties in the table after refresh seems to work as a workaround. However, is there a way to configure Power Query to automatically update the table without requiring this manual step? This issue is affecting all my colleagues, and the resulting data suffers from this delay.

***Moved from Microsoft 365 and Office / Excel / For business / Windows***

Answer 1

Hello, StokesXD

I understand the issue with Power Query not loading cells with numbers is persistent. Here are some troubleshooting steps to consider:

1. Verify that the data types for the columns in Power Query are set accurately. If a column is mistakenly set as text when it should be a number, it may not load properly.

2. Check that the original data is free of any hidden columns/rows or merged cells, as these can disrupt the results produced by Power Query.

Lastly, attempt to delete the output table (Make sure to create a backup file before proceeding), then reopen the query and load it anew. This should regenerate the table structure. If possible, please share the structure of your output table, either through a screenshot or file share.