Old data not clearing when refreshing

(0) Share

Report

Posted on by Graemess

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

Categories:

Power Query

I have the same question (0)

All responses (4)

Answers (0)

Graemess 10 on at

Like (0)

Report

Thank you very much for replying Stephen,
I am using Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20806) 64-bit
I have a lot of BAU atm so I can't spend too much time on this side project right now. (I am trying to develop my PQ skills so I spend less time on BAU!)
I have tried option 1 and cleared the cache but unfortunately it did not fix the problem.
I now realise that when I change the tab in the APPLIED STEPS navigation it says that it is on Oct and not Sep tab in the query. When I close and load it shows the correct number of rows loaded for Oct but when I look at the data it is the same data as Sep.
Oct 23,323 rows loaded but the numerical count is 23,419 which is the Sep data.
So it seems I was wrong, I thought it was not clearing the extra rows from Sep but it hasn't loaded any of the new Oct tab data.
I will continue to work on it on Thursday or next week.
Thank you again for your help.
Graeme

Power Query Data ...

Was this reply helpful? Yes No
Graemess 10 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Graemess 10 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Graemess 10 on at

Like (0)

Report

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

Was this reply helpful? Yes No