Forum Discussion
Updating existing sheet with new data from another workbook
Hewtee Not sure if I fully comprehend your issue, but I'll give a try.
When you load data from a folder, you first need to filter the file names that contain e.g. "RawData". That will then give you a list of files like you describe, but with different suffixes. I assume that the latest downloaded file also has the most recent time stamp of them all. Then you can filter the most recent file from the "Date created" column (Date/time Filter, Is Latest), and then press the two downward arrows in the Binary column. A few helper query and a RawData (name of the folder I used to test this) query are created. The latter contains the data from the most recent file. Do your transformations/filtering and close and load back to an Excel sheet to form the basis of your other sheets.
Now, add a more recent data file to the designated folder, making sure the file name contains "RawData" (or whatever you filtered on in the beginning). Go to the Excel table you loaded earlier, right-click in it, choose Refresh, and the most recent data will be loaded.
Riny_van_Eekelen Thank you, I will send them now 🙂
Hewtee Not sure if I fully comprehend your issue, but I'll give a try.
When you load data from a folder, you first need to filter the file names that contain e.g. "RawData". That will then give you a list of files like you describe, but with different suffixes. I assume that the latest downloaded file also has the most recent time stamp of them all. Then you can filter the most recent file from the "Date created" column (Date/time Filter, Is Latest), and then press the two downward arrows in the Binary column. A few helper query and a RawData (name of the folder I used to test this) query are created. The latter contains the data from the most recent file. Do your transformations/filtering and close and load back to an Excel sheet to form the basis of your other sheets.
Now, add a more recent data file to the designated folder, making sure the file name contains "RawData" (or whatever you filtered on in the beginning). Go to the Excel table you loaded earlier, right-click in it, choose Refresh, and the most recent data will be loaded.
Riny_van_Eekelen More' meneer (my attempt as I speak Afrikaans:)
Ok I have got everything working thanks to you, however I selected combine files instead of merge. Is there a way to change the existing query or do I have to start again?
Thanks Hugh
Hewtee Again, not sure I follow, but if you intend to combine all the files (within a folder/sub-folder) that contain "RawData" in the file name, just leave out the step that filters for the most recent file. Then all files in the folder that match your criteria should be picked-up and "joined" into one query automatically. Add a newer file, refresh and its data will be added to the output. No need to create a new query.
Riny_van_Eekelen Morning.
Ok I see what the problem is.
Lets say my first Workbook that I load has 10 rows. The next one (later date) has 14.
What the query does is add all the data into 24 rows and so its duplicating the common data from both workbooks. What I want is it to just "add" the new data.
I have attached the file and a screenshot of power editor.
I an concerned that I am abusing your kindness, if so I will post this question in the forum?
Riny_van_Eekelen Wow thank you. Ill follow your suggestions and let you know 🙂
