Forum Discussion
Update a table from external file weekly
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?
Apologies for not being clear.
I'll try to explain it better.
May I clarify
- you have "old" file (File B)
- you have another file with "external" table (File A)
- you have master table, not clear in which file (File B)
- you have weekly files (yes basically I end up having weekly Files A & B, one I receive, one I make)
I receive File A with data in a table every week, as data in it is added and updated weekly.
When I first received it, I made File B (or old file) as described below (done by cut and paste the table in File A into File B). To this table, I have basically added columns with formulas and created a "master table" which I use to do my analysis via pivot tables.
With that
- you take data from weekly file and copy/paste it to the file with external table (no I take the table in the external File A and copy in file B, basically replacing the portion of the master table, every week. My calculation columns with formulas remain there as the table I copy in has always the same headings, just the data and the number of rows change)
- you do some transformation with external table and append it to the master table in another file (yes, but I don't do anything in File A, which has only the table. The columns with formulas are only in file B)
Thus at every moment you have
- raw weekly files Yes, file A
- weekly files with transformed data Yes, file B with my master table
- file with master table which consists of all weekly transformed data- you rename transformed file with with external table and save it as weekly updated file Yes
Something like this? And if so do you really need weekly files with transformed data, or that's only kind of backup?
I't not a backup. By having a weekly updated "master table" I can check (via pivot tables & graphs)what's going on in terms of the number of customers, sales, time of response to queries etc.
You can see my process is not efficient and the bigger the table becomes, the more complicated will be working with thousands of rows and 30 columns.
