Data model for a double-entry bookkeeping system in Excel/PowerPivot?

[pqv]

The technical goal that I want to achieve is as follows. Note that this is a superficial example for technical explanation only:

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.

[MattAllington]

Interesting. Can you post a sample workbook?

[Rettos]

[quote="pqv"]The technical goal that I want to achieve is as follows. Note that this is a superficial example for technical explanation only:

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 [i]table names[/i] 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).

[img]https://i.imgur.com/tGjK57E.jpg[/img]

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


Have you solved the problem ??