Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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..
Solved! Go to 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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.