I have three tables in Excel. One is called Expenses, and the other two are called Food and Candles respectively. Each table has the columns Date, Payee, Amount and Transfer. The Transfer column accepts table names only. If I buy some food and candles, appropriate entries should be made in the Expenses table with each transaction properly referenced in the Transfer column ("Food" and "Candles" as appropriate). The transactions then show up in the relevant Food and Candles tables, each with "Expenses" referenced in the Transfer column. Data added to any of the tables in the worksheets appears in the other relevant table(s).

Can this be done with PowerPivot and Power Query, and if so please could someone explain how? Thanks.