Forum Discussion
Helper VDesign Advice: Accounting Consolidations Best Practices
HI ImkeF
I am building a model to consoldate 4 Supplier Ledgers and was wondering if you had previously blogged about this process on your biaccountant website?
I am aware that creating bi directional relationships maybe overcomplicating model and or making model run less efficiently... Is there new methods to handle these kinds of requirements...maybe like using TREATAS or some other virtual relationship options?
I was looking for the best approach to:
- Consolidate Tables or the same structure together for easier reporting
- Grouping tables so single measures can output outstanding balances ie. Report by Supplier Name, Business Name.
- Break up each invoice by PurchaseTypeID
- The Top Level Key is the Company Files Table holding each data files specifics.
- The MergesSuppliers Table is the result of combining all the SupplierCard Tables (1 to 3). I have done this to avoid having to expose end user to each of the SupplierCard Tables 1-3 to obtain each supplier "Name" field from each compan file table. I can then hide each of the sub tables 1 to 3 and just leave the merged version
Supplier Consolidation Model
Has anyone tried consolidations in Power BI previously? I am looking to provide the end user the least amount of tables data to look through..
Hi THEG72
No, I haven't blogged about it yet.
But when I do consolidations, I use only 1 table and add a column to it for the different company names (same goes for the combined keys with the dimension tables).
As long as you leave the signs from the bookkeeping figures untouched, the figures fall automatically into place then.
Might be worth a blogpost actually ;)
Is the table structure of your 3 Supplier ledgers tables the same? Is the table structure of your 3 Supplier Card tables the same?
I would personally turn 'Enable Load' off on your Ledger and Supplier card tables create new tables using:
Source = Table.Combine({SupplierLedger1, SupplierLedger2, SupplierLedger3})Source = Table.Combine({SupplierCard1, SupplierCard2, SupplierCard3})If you need to be able to filter back to the original tables, create a new column in Power Query for each table in its own code that simply holds a text string you can filter on. You could therefore remove the MergedSuppliers table.
From here, you should be able to do most of what you are talking about using measures.
11 Replies
- ImkeF
Community Champion
Hi THEG72
No, I haven't blogged about it yet.
But when I do consolidations, I use only 1 table and add a column to it for the different company names (same goes for the combined keys with the dimension tables).
As long as you leave the signs from the bookkeeping figures untouched, the figures fall automatically into place then.
Might be worth a blogpost actually ;)
THEG72Hi ImkeF
When you say you use only 1 table...does that mean you are consolidating the Supplier Cards into one table and the Supplier Ledgers into one table and then writing the consolidated measures from there...as opposed to writing measures for each ledger grouping...So using a combine table step first
Are you still enabling the reload on the orginal source ledgers for each entity?- ImkeF
No need to have them at all:
Ideally, you can import from folder and just expand your binaries there into one (consolidated) table.
But if your source-files aren't in one folder, you can simulate this behaviour by putting all adresses to the files into one column (one row per file) and apply a function to open them in a new column. (
Is the table structure of your 3 Supplier ledgers tables the same? Is the table structure of your 3 Supplier Card tables the same?
I would personally turn 'Enable Load' off on your Ledger and Supplier card tables create new tables using:
Source = Table.Combine({SupplierLedger1, SupplierLedger2, SupplierLedger3})Source = Table.Combine({SupplierCard1, SupplierCard2, SupplierCard3})If you need to be able to filter back to the original tables, create a new column in Power Query for each table in its own code that simply holds a text string you can filter on. You could therefore remove the MergedSuppliers table.
From here, you should be able to do most of what you are talking about using measures.
- THEG72
Hi Anonymous ImkeF,
Thanks for your replies. The table structure is EXACTLY the same for all ledgers... its from an accounting database by just differnet connections to each business file.
I was looking to see whether it was better for efficiency to combine the Supplier Card Table and Ledger data first and then do the measures or create measures for each table group and then sum the measures for group totalling?
I also need other dimensions like PurchaseType (Item or Service), OrderStatus (open, closed, quote) which are the same accross all fact tables.
When a new supplier is added to the live supplier table 1and a refresh is scheduled....will your Table Combine work if you dont enable Load on the SupplierLedger1 source?
Enable Load is a very confusingly worded option. The table does refresh and accept new data but isn't loaded into the data model for use in your reports.
I also noticed in your reply you mention you need other FACT tables etc. In my experience I've found this to be worse in Power BI than simply loading in the tables you need and relying on measures to do the heavy lifting. The Vertipaq engine that Power-BI is build on is fantastic for data and using FACT tables like you would in SQL just bloats and slows down your model from my experience.
When i picked up my current role, the early produced PBIX files were full of staging tables and fact tables. After stripping them out and optimising the data held, i often shrunk the PBIX files by around 20% and sped up complex queries.