Update a table from external file weekly

[KLolli]

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.

[#2]

@KLolli 

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.

[#3]

@Sergei Baklan 

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.

[#4]

@KLolli 

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?

[#5]

@Sergei Baklan 

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.

[#6]

@KLolli 

Thank you for the clarification.

If files A have the same structure in general that's feasible to do with Power Query, but that's not half an hour project.

You may collect all files A (weekly ones) in some folder. File B (master) is in another place. Power Query in File B on refresh checks if new weekly files appeared, query, transform each of them and combine all together into the master table adding data to data model. Instead of formulas additional calculations and adding of columns could be done by power query.

Pivot tables are created from data model and updated on same refresh. If place File B on Sharepoint, connect it from any Power BI workspace and schedule the refresh, all process could be performed automatically and the only manual operation is to add new files to the folder. On the other hand also could be automated with Microsoft Flow.

Sure, evil is in details and above is only very raw approach. Many things depends on concrete data structure and transformations are to be done.