Page 1 of 1

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

Posted: Sat Sep 01, 2018 11:40 pm
by 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).

Image

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

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

Posted: Wed Sep 05, 2018 12:14 pm
by MattAllington
Interesting. Can you post a sample workbook?