Daily File Update/Overwrite

[MODUser]

Created a query to pull a daily csv file, for which I have no creation control, that is auto saved to SharePoint. With each new daily file I need to overwrite the dated data in the existing query table with the new file data. Each file has the same format and titled with the run date and file name (name does not change), that is...20231012_File Name, 20231013_File Name etc.

For example, if the existing query table has dated data from 1/1/2023 through to 10/12/2023 and the new day's cvs file has dated data 10/2/2023 - 10/13/2023, I need to, essentially, delete all rows in the existing query table having the dates 10/2/2023 - 10/12/2023, then add the new file 10/2/2023 - 10/13/2023 data to the existing query table. How can I accomplish this within Power Query?

[MODUser · Accepted solution]

Found a solution that worked for me....

Remove Duplicates and Keep Most Recent

[edhans]

1. Get the dates from the new table.
2. Create a list of those new dates.
3. Use a filter. See https://www.ehansalytics.com/blog/2020/5/20/using-listcontains-to-filter-dimension-tables for an example. However, you will add a 'not' in front of the List.Contains() function to filter those records out.
4. Append your new table to the existing data

[MODUser]

@edhans  Apologies for the late response. How does this change if the the goal is to do this within Power Query Editor?

[edhans]

My steps above were for Power Query. You need to provide us some examples so we can help. 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[MODUser]

I walked through the process with the following issues/concerns...

1. The daily file has dates that overlap the prior day's daily file and the main file...how do I keep/save the updated existing data file each next day in order to apply the next day's daily file updates? For example, main file is 1/1/2023 through 10/14/2023. Monday's daily file is 9/30/2023 - 10/15/2023 and Tuesday's daily file is  10/01/2023 - 10/16/2023. Using the List.Contains filter on the main file on Monday removes the data rows dated 9/30 - 10/14 from the main file, then through the append step adds Monday's daily file data rows 09/30/2023 - 10/15/2023 to the main file. On Tuesday, I need to apply the Tuesday daily file to the Monday appended main file now having data rows dated 1/1/2023 - 10/15/2023... and so on each day.

2. The main file has over 10 million rows and continues to grow with each new daily file, and the close/load is taking a long time to load..in 10 minutes only 150 rows loaded and the load was eventually halted. Enable Load is unchecked for the daily file table and the reference list and I was only loading the updated main file.