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,
I need to create an P&L Report defined into Navision's Account Schedule that look like this:
I have searched a lot of resources, and one is close enough to what I need, and I would like to thank @ImkeF for this great article: Easy Profit and Loss and other (account) scheme reports in Power BI and Power Pivot using DAX - the picture above is taken from the mentioned article in the https://www.thebiccountant.com/.
The problem is that I have the Account Schedule in this form:
With another article from the same author: Create list of accounts from NAV account schedules or COA totaling syntax I have managed to breake the Totaling column for Posting Accounts but I'm stuck (blinded) on how could I properly transform/manage the Formula totaling_type lines.
Is it possible in this scenario to get the report?
EDIT: A more concrete question would be: How can I "split" the formula rows (ex. 10..30) based on Row_No column into corresponding Totaling values from that row (ex. 10 => 11100..11299) so then could "split" it again into the corresponding accounts using formula from the "Create list of accounts..." article.
Kind Regards,
Lucian
Solved! Go to Solution.
Hi @Lucian
you can use my function to recursively solve this from here: https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-po...
So first create a list of all items from the current row with my NAV-function that you've already found, expand and then use the above function to determine the levels / retrieve all parents.
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
Hello again,
Meanwhile I think I get "closer", but I still need help. It seems that the main problem is to transform the Accout Schedules table.
So I try to start from a simpler model:
So the problem is how could I replace formula lines with the Totaling values from the corresponding Row_No .
For example 10..12 should be replaced with 11100..11110|11300..11310|11500..11510
Basically something like this:
Splitting the Totaling and the "first level" formulas using the NAV.AccountListFromTotaling.pq from the article in the previous message I could solve it using a "self join" (maybe not the best option), but still remains one problem: The formula on the last line (please disregard the description from all of them).
In this case how could I convert
95|165 => 10..12|100..102 => 11100..11110|11300..11310|11500..11510|21200..21210|23200..23210|25000..25010
And this formula is "just" 2 levels but what if will be n levels?
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Invoked Custom Function" = Table.AddColumn(Source, "NAVSplit", each NAVSplit([Totaling], null, null)),
#"Expanded NAVSplit" = Table.ExpandListColumn(#"Invoked Custom Function", "NAVSplit"),
#"Added Conditional Column" = Table.AddColumn(#"Expanded NAVSplit", "LooukupFor", each if [Totaling_Type] = "Formula" then [NAVSplit] else ""),
#"Merged Queries" = Table.NestedJoin(#"Added Conditional Column", {"LooukupFor"}, #"Added Conditional Column", {"Row_No"}, "Added Conditional Column", JoinKind.LeftOuter),
#"Expanded Added Conditional Column" = Table.ExpandTableColumn(#"Merged Queries", "Added Conditional Column", {"NAVSplit"}, {"Added Conditional Column.NAVSplit"})
in
#"Expanded Added Conditional Column"
Any ideeas?
Kind Regards,
Lucian
Hi @Lucian
you can use my function to recursively solve this from here: https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-po...
So first create a list of all items from the current row with my NAV-function that you've already found, expand and then use the above function to determine the levels / retrieve all parents.
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
Hello @ImkeF ,
I felt like I was missing something... it was just "another" good article from your site. 😉
Thank you very much for your help.
Kind Regards,
Lucian
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.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |