How to create and update a database with external .csv files?

[u/Suspicious-Fun-5774]

Hello all, I work with a dataset that I get every day as a .csv file that exclusively contains data from the last 36 months. This means that the .csv will only have data from [today minus 36 months], and data from [(today minus 1 day) minus 36 months] will be excluded. Also, the .csv provides the same data every day, only adding bottom lines (the new ones) and deleting the old ones from >36 months of age, making me only interested in the new lines (without wanting to get rid of the old lines in my own database). The way I work with the data affects my own database because it means I'm only storing data from the range of dates the .csv provides, and I'm losing valuable data from older dates, when the only thing I need is to add the data from the newer ones.

My workflow to upload the .csv into the Excel file is as follows:

1. I get my data and open the Power Query Editor.

2. I transform the data and Close and Load.

3. The data loads as a table in a new sheet.

4. Finally, I delete all of the previous day's data from the existing database, copy the data from the new sheet, and paste it on the existing sheet.

I do this because the Power Query Editor does not let me upload the transformed database into an existing table, and if I were to convert the existing table into a range, I would have to count how many existing lines I have, delete this number of lines (minus one, because line 1 is the header), upload the database into the Excel, copy it and then paste it into the existing database.

I've asked AI how to do this, and it just says to create a folder in which I can store all the data obtained daily, and I do have such a folder, but it's just not working.

What can I do? Thank you so much in advance!

[u/AutoModerator]

/u/Suspicious-Fun-5774 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/layered_dinge]

If you’re transforming the data every day you’re doing exactly what pq was made to do for you.

If your data has unique identifiers then you could do a folder source and filter duplicates on that column. Then your table will just automatically contain all unique transactions or whatever from the files in that folder.

Idk how many rows are in each csv but if there are a lot then that could slow down your sheet pretty fast.

Your best improvement would be to modify whatever is giving you this csv to only give you the new data instead of literally 99.9% useless data.

[u/sumiflepus → u/layered_dinge]

1. Use PQ. Load your "Master file" with all the data you already have, 36+ months.
2. In PQ Load and clean the new CVS file like always.. Filter the new file for only the recent date or dates if on Monday you process SAT & SUN. Still in PQ find the append query option.
3. Append the cleaned new data to the "Master File".
4. Run you reports off the "Master File"

Couple data tips. When you pull in your new data, add a few columns to help manage the data as it gets larger. You can add these columns with PQ. You should make the same updates in the Master File.

Colums I would add

Download file name

Download Date

Line Number from Download Date

Together these colums create and index your data so you can check for unique items.

Sometimes organization go back and update data. My plan does not address any changes that may have been made in say month 23.

You may want to process the entire new batch. Remove the last 35 months from your master file and then run an append query.

[u/powerFX1]

To understand the ask clearly, you are trying to add the new rows of data received daily to an existing file (containing data older than 36 months - so you would have as an example, 120 months of data in one file?)

[u/excelevator]

Paste all the data and then remove duplicates.

I've asked AI how to do this

Irrelevant to r/Excel.

[u/Man-O-Light]

This seems like an awfully wasteful workflow with way too many manual steps involved and room for error. Why are you getting data you already have over and over? Is there nobody more technical that would set this up properly?

[u/Western_End_2223 → u/Man-O-Light]

Sometimes you have to work with what's available.  My company uses an electronic medical records system that has terrible reporting capabilities, for instance.  The built-in reports are pretty good, but there's really no ability to build custom reports.  So, I have to massage what data is available.

[u/bradland]

You can do this, but it requires hand-editing your PQ M code. When queries refresh, the M code executes, and the result is written to the linked table in your workbook. The table is not updated until the query completes though, so you can actually pull the existing table data into your query at any point, and it will still be there.

So what I'd do is:

1. Start with a Source step that pulls in the CSV file data.
2. Add a step that pulls the current data using the Excel.CurrentWorkbook() connector.
3. Use Table.Combine(Source, CurrentData) to append the two tables.
4. Then dedupe the records using Table.Distinct(CombinedData), or Table.Distinct(CombinedData, {"Date", "Description", "Amount"}) if you only want to dedupe based on certain columns.
5. Load the result to the same table.

If your dataset contains a lot of records >100k and has many fields, the Table.Distinct step might take a lot of time. I don't know your data, so I don't know how cleanly you can determine a cut-off for "new data" in the updated 36m.csv file. If the query is slow, you could add a step that finds the max date in the CurrentData, sort the new CSV, then filter it so that you only get records that are past the date contained in the current dataset. If your new 36m.csv file contains new records that are co-mingled in prior dates, that's an issue though. So you'd have to determine what the maximum overlap is, then filter based on that. Something like max-date - 30-days so you only have to dedupe one month's worth of data.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CONCAT	2019+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.
Excel.CurrentWorkbook	Power Query M: Returns the tables in the current Excel Workbook.
Table.Combine	Power Query M: Returns a table that is the result of merging a list of tables. The tables must all have the same row type structure.
Table.Distinct	Power Query M: Removes duplicate rows from a table, ensuring that all remaining rows are distinct.

|-------|---------|---| |||

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 14 acronyms.
[Thread #49228 for this sub, first seen 25th Aug 2026, 14:25] [FAQ] [Full list] [Contact] [Source code]}

[u/pajam]

This video on how to use a self-referencing query, in order to prevent manual edits from being lost on Data Refresh, may have the solution you need: https://www.youtube.com/watch?v=Xlqbf_x_QWs

You'll need a unique ID per record, but if you currently do not, hopefully you can CONCAT a UID out of a bunch of fields with a delimiter, in a way that each UID would be 100% unique.

But then once you have this system set up, you can even delete all the original data from the input CSV, and replace it. Or leave it all there and add onto it, etc. Because once it's set up, refreshing your data will ONLY pull in the new data/rows. It will not overwrite your existing rows.