In the Power Query course, you had a lesson on keeping the monthly files in one folder named "sourcefile". This has been an invaluable bit of information in my power queries.
I am looking for a method of updating monthly Pivot data sources. Each month, I receive a report of legal cases. There will be additional line items, and also changes of status for existing cases. I've started off with March 2019 data.
QUESTION: When the April 2019 data comes out, how do I update the file for the new dataset, and keep the data model in tact?
Thank you so much!
Hi Allison,
Do you want to add the new data to the query or replace the old data with the new data?
If you want to add to it then you add your new file to the folder containing the old file. You'll want to differentiate the files upon import to Power Query with some form of versioning like a date or batch number. You could use the file name to do this by retaining it when you merge the files in Power Query.
Whereas, if you want to replace it then you remove the old file from the folder and replace it with the new file.
Mynda
HI Mynda,
This is for Power Pivot though.
Ideally you should be loading your data to Power Pivot via Power Query, not direct to Power Pivot. Power Pivot can't get data from a folder, only Power Query can do that. If you want to replace the data each month, then you'd just replace the old file with the new file. All column headings would need to be identical.
