Duplicate data on Files - Take most recent Data

[MStark]

Hi,

We have data saved in a file daily with Sales amounts per day for the past 14 days. Each day there might be some updates done to previous days which is why we pull for the past 14 days instead of just yesterdays data. How can we have Power Query take the data from the most recent file and the oldest date from the past files? 

Ex - yesterday we saved a file with dates 3/5-3/18. Today we saved for 3/6-3/19. I want power query to take data from 3/5 from yesterdays file and 3/6-3/19 from todays. 

Can that be done?

Thanks in advance for your help and assistance!

[mussaenda]

Hi @MStark,

is it only one file and you are adding the data?

If that is the case, we can simply put a dynamic filter like this

So everytime you will have a new data, the power bi will automatically filter your data.

Instead of declaring a static dates.

Hope this helps

Thanks

[MStark]

Unfortunatly not. As said in the example, each day a file with the previes 15 days data gets saved

[ND_Pard]

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 🙂

[MStark]

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

[ND_Pard]

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.