Forum Discussion
chart of accounts alignment between two different systems
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 |
|
Table 2
Ps. sorry for the ugly table examples. I seem to be quite bad at adding tables to my posts..
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"
3 Replies
- ImkeF
Community Champion
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
Super User
Can you please try to explain it again? where did the date come from? where did the from/to accounts go?
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
