Forum Discussion
Duplicate data on Files - Take most recent Data
Use Power Query to create a query named: Yesterday's File that filters only the date: 3/5
Then use Power Query to create a query named: Today's File that contains all of todays records.
Finally use Power Query to Combine Queries using the Append feature. This query will return the desired records for 3/5 thru 3/19.
Good Luck 🙂
That would work if we only had 2 files. But if we continue with this process of saving files each day, cant create all those querys
We need a way that query would know to delete duplicates and keep most recent data on all files
Then consider a naming convention that includes either the most current date or the oldest date as part of it's name for each file, such as: Sales_Amounts_2023-03-19.
If the naming convention is consistent, you can put a formula into 2 Excel cells that displays the current date and the previous days date. Another cell could calculate the day 15 days prior to the current date.
Give the cells range names, example: Today and Yesterday and 15_Days_Ago
Then, using the technique shown in:https://www.howtoexcel.org/power-query/how-to-reference-a-named-range-in-power-query/
reference the range names in the source of your power query and/or in the filters you set for a specific date.
The queries will then not need to be rewritten as they're always using references to the two most current files and the dates needed to filter your data.
Good Luck.
