Old data not clearing when refreshing

[Syndicate_Admin]

Hi team,

I am a an experienced intermediate Excel user but a Power Query novice.

I have a reference workbook with a list of Active Employees for each month in a separate tab. Around 23k rows per month.

 I have several monthly reports that uses the Active Employees reference workbook with simple PQ that 1) pulls the data from a SharePoint folder, 2) navigates to the month's tab, 3) promotes headers and 4) changes type.

Our practice is to take a copy of last month's report and rename it for this month. I simply Edit the PQ to navigate to the new tab for this month. It has done the job successfully until now.

The problem I have encountered is that this month there are less rows than last month. When I run the report the remaining rows of last month's data from where this month ends are left there.

To illustrate more clearly, September had 23,500 rows and October 23,000. When I run the query I get November data until row 23,000 and then 500 rows of September data after that. Can anyone help me figuring out a query that completely clears last month's rows?
Thank you,

Graeme

[Syndicate_Admin]

Please see the post below with the Power Query not updating attachment.

[Syndicate_Admin]

Hi Stephen et al,

Thank you for your original reply. I am getting the hang of providing enough information. To reiterate, I was mistaken about not clearing the final rows of the previous data.
It appears that PQ is working correctly referencing the new tab and showing in the summary the correct number of rows but there is no change to the data. It is stil showing all the Sep tab data and not the new Oct tab data.
Thanks for your help

Graeme

[Syndicate_Admin]

I think the account may have been hacked. It looks like someone is advertising for cheating on assignments.

[Anonymous]

Hi Graemess,

From your description, it seems like the old data is not being cleared out when you refresh your query for the new month. This is likely due to the fact that Power Query is retaining the old data in the cache. Here's a step-by-step action plan to help you clear the old data:

1. **Clear Power Query Cache:**

- Go to the `Data` tab in Excel.

- Select `Queries & Connections`.

- In the `Queries & Connections` pane, right-click on your query.

- Choose `Refresh`.

- If this doesn't work, you can try to clear the cache by going to `File` > `Options and settings` > `Data` and under `Data Cache Management Options`, click on `Clear Cache`.

2. **Adjust the Query to Remove Excess Rows:**

- Open the Power Query Editor by clicking on `Edit Queries`.

- Once in the editor, add a step to remove rows that are not needed.

- You can use the `Remove Bottom Rows` option under the `Home` tab to specify the number of rows to remove.

- Alternatively, you can add a filter to only include rows that have data relevant to the current month.

3. **Save, Close, and Load the Query:**

- After making the necessary changes, click `Close & Load` to save the query and load the data into Excel.

- Verify that the old data has been cleared and only the current month's data is present.

4. **Check for Errors:**

- If the issue persists, check for any errors in the query steps that might be causing the old data to remain.

- Look for any error messages or warnings in the Power Query Editor and address them accordingly.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.