Using PowerQuery to move and store records being generated in one table to a "Master" Table

[fraljones]

I have developed a file that allows a user to choose a project from a list and then input forecasted expenses into a box. 

The file only allows for one project to be forecasted at a time; once the user inputs the expense, rows are created using formulas. 

These forecast rows are combined with budget & actual data with the help of PowerQuery to create a pivot table.

I am having trouble creating a method using PowerQuery to move the formula generated rows (residing in table #1) into table #2, where table #2 can then be updated/refreshed in order to capture new rows from table #1 from the various projects the user inputs expense for. 

I apologize if the description of the scenario isn't clear; also thank you for the help in advance.

[Nathaniel_C]

@fraljones ,

Not sure if I understand, but have you tried the append function in Power Query using new table to join the two tables together in a third table, and then delete uneeded columns? Maybe you can give us examples?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[fraljones]

@Nathaniel_C  I updated the below chart to better explain my problem:

As mentioned in the chart, I am having difficulties trying to figure out how to: 

1. Send new records from Table #1 to Table #2

2. Keep all old data in Table #2

Thank you for your help Nathaniel.

[Nathaniel_C]

@fraljones ,

Ok, going off line, but will be thinking about that and get back to you tomorrow. Clear and concise explanation.

Nathaniel

[Nathaniel_C]

Hi @fraljones ,

Tried various methods, I think you are going to have to export your table 1 to a folder as .csv or Excel file. Then use import all files in a folder. I am sure that each csv could be renamed by including date, So Table1 5/12/2019 or something like that using VBA. Then use Import from Folder. @KenPuls has written a great book with Miguel Escobar M is for (Data) Monkey   In Chapter 4 Importing All Files in a Folder he shows how to do this very easily.  

So you could get his book on Amazon quickly, or perhaps @ImkeF  might have suggestions for  you, or here is a Link to support office . Microsoft office support on this subject. 

Good luck!

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[ImkeF · Accepted solution]

Hi @fraljones ,

if my understanding is correct, this should do the job: https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or...

[fraljones]

This absolutely worked! Thank you @ImkeF and thank you @Nathaniel_C for the reference!

[Nathaniel_C]

Thank you @ImkeF !