chart of accounts alignment between two different systems

[ErikBe]

Hello masters of PowerBi,

 I am sitting with two different business systems and with them, two separate chart of accounts. One very detailed (the chart of account in the ERP system) and one more aggregated (the consolidation system's chart of accounts).

In PBi, I would like to be able to tie the ERP account transaction to the specific consolidation system account it is mapped to. The mapping structure is presented below as table 1 whereas table 2 is the the facttable I want to add an additional column that shows the consolidation account.

Fyi, in table 2 to I have actually already added the desired column just to give you an idea what I am after. 

I hope I've left enough info and that someone has the time to help me. Regardless, have a good weekend and stay safe.

Best regards,

Erik

Table 1 

From account	to account	consolidation account
501000	501000	501000-000
502000	502000	502000-000
503000	504000	503000-000
506000	506200	506000-000
507000	507000	507000-000
509000	509000	509000-000
521000	522000	521000-000
541000	541500	541000-000
542000	542000	542000-000
544000	544000	544000-000
546000	546500	546000-000
545100	545100	546000-000
546600	546600	546600-000
546700	548000	548000-000
551000	551000	551000-000
559000	559500	559000-000
561500	561500	561500-000
561600	561900	561900-000
571000	571100	571000-000
572000	572000	572000-000
573000	573000	573000-000
579000	579000	579000-000
581000	581200	581000-000
582000	582000	582000-000
583000	583000	583000-000
584000	584000	584000-000
596100	596100	599000-000
599100	599199	599100-000
605100	605100	605000-000
605000	605000	605000-000
607000	607200	607000-000
609000	609200	609000-000

Date	ERP system account	Amount	Consolidation Account
2020-01-01	501000	25	501000-000
2020-01-02	503500	100	503000-000
2020-01-03	559500	5000	559000-000
2020-01-04	561900	2000	561900-000
2020-01-05	609100	10000	609000-000

Table 2

Ps. sorry for the ugly table examples. I seem to be quite bad at adding tables to my posts..

[ImkeF · Accepted solution]

Hi @ErikBe ,

you can add a column to your table the creates one row for each potential account number: {[From account]..[to account]}

This is the full code that you can paste into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZJBCsMwDAT/knMLtmvL0VtK//+NEvDUu6G3yTJBWuH3+xilllKOxx2e18fncQmN3ECEF3kHXi4EeTTAhYlgIEKSG2yhsXxjyeYtOkKvA3CBHx1EoJ2DCNTswQiv2Udd+Q9uQgS5ggpz5WcR2MKgpoMIHHDkAEwI7uOgQpCnwBYmo2etgI2YXNhBBF6Ugwi0cNjCyQ5nbYALjHYQgdEOIvAMHLaQsepv8CUzf3nNXCBClPVOFIoLRfJ/wiSfDXAhERJhL/n5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"From account" = _t, #"to account" = _t, #"consolidation account" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From account", Int64.Type}, {"to account", Int64.Type}, {"consolidation account", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "AccountNo", each {[From account]..[to account]}),
    #"Expanded AccountNo" = Table.ExpandListColumn(#"Added Custom", "AccountNo")
in
    #"Expanded AccountNo"

[lbendlin]

Can you please try to explain it again? where did the date come from? where did the from/to accounts go?

[ErikBe]

Hi Ibendlin,

Thank you for the reply. The dates are tied to the transactions and come from the transaction table the second table in the original post. The from/to accounts comes from the static mapping table (the first table in the original post).

best regards,

Erik