Combining Two Chart of Accounts

[baldburke]

I have two separate chart of accounts from two financial systems that I would like to create a single relationship for from two tables.  For each of those chart of accounts I also have two companies.  The first chart of accounts uses a six digit string and the second uses a four digit string.  For the separate companies I use single let to identify each.  The first company has cash accounts as F111000 and L111000 where the second company is F1000 and L1000 for the eqiuvalent accounts, respectively.

This is all very straightforward so far except for the fact that in some instances we do not have 1:1 alignment.  As an example, the second company uses a single account to cover salaries and wages where the first company breaks out direct labor, indirect labor and salaries all into separate accounts.  So account F6000 on the second company aligns with F600000, F605000 and F610000 on the first. 

I thought I would add a third table to join everything.  I compiled a list of 402 distinct records that had a unique identifier to accomodate all the possible combinations between the two chart of accounts with each having two companies.  However, I can't get a true 1:1 relationship on the second table because of the account replication previously noted.

If anyone has encountered this problem before and has suggestions then I would happy welcome them.  My goal is to run two trial balances by month for each and merge the data togehter to ensure that we have a full match.  It should be a straightforward process, but we are still having to manually put these reports side by side to compare.  

[v-easonf-msft]

Hi , @baldburke 

Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

[Anonymous]

i would create an chart of accounts dimensional table with an index column 

then you can use the index column to connect all of the accounts it also makes it easy to adjust and make changes as needed 

i do this all the time for different customers 

if you have any challenges let me know 

[Ashish_Mathur]

Hi,

In the first table, create another column which lists down the accounts (based on the third table that you have creaed with 402 distinct records).  Then you build a relationship from the Accounts fields of the 2 tables to the third table that you created.

Hope this helps.

[Greg_Deckler]

@baldburke - Sample data would be helpful to visualize what is going on here. Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.