Forum Discussion
Update a table from external file weekly
Hello,
I need some help to find the best way to analyse a table I receive updated weekly. I usually copy and paste it in a different file where I have my formulas so that I can merge both tables in one and I only need to add again conditional formatting and update my pivot tables.
Is there a way to automate this process without cut&paste so that by changing the file source for the table my file is also automatically updated?
Many thanks in advance.
K.
5 Replies
It depends on how do you use resulting data, but perhaps Power Query could help:
initiate first time the table returned by query of source file; query this result and new source, append one to another and return to the same resulting table. The only is to add some logic not to append same update several times. That could Remove duplicates if records are different, or other logic.
Thank you very much for your reply.
I am not familiar at all with Power Query, therefore you have to excuse me if I need a more detailed explanation.
This is what I currently do:
I have an old file where I copied (CTRL+C/CTRL+V) the "external" table that I was sent some time ago. In the first rows, I have added my formulas. I have converted the "external" table to Range, and made a table again including my to this master table, so I can analyse the entire dataset with pivots.
I take this file and save it with a different date and replace the "external" table this way each week.
How would Power query work in my case?
Ideally, I would like to be able to update my master table each week having a reference to the new file I am sent without having to cut&paste the table every.
Thank you in advance for your time.
K.
May I clarify
- you have "old" file
- you have another file with "external" table
- you have master table, not clear in which file
- you have weekly files
With that
- you take data from weekly file and copy/paste it to the file with external table
- you do some transformation with external table and append it to the master table in another file
- you rename transformed file with with external table and save it as weekly updated file
Thus at every moment you have
- raw weekly files
- weekly files with transformed data
- file with master table which consists of all weekly transformed data
Something like this? And if so do you really need weekly files with transformed data, or that's only kind of backup?
