Sum by account level

[pedroccamara]

Hello everyone
I believe this one is very tuff and i don’t know even where to start. That’s why i’m asking your help.
I have an accounting table of all the values by each account id. As you all know those account id can be for movements, integration or reason. Those last two are the sum of the movements account that “belongs” to them. Let me give you an example

I would like to know the sum for each account, as long as they are “R” or “I” because these ones doesn’t even exists in this table of movements.
I’m attaching a file.
Thank you very much for your help
Pedro
account resume.xlsx (17.0 KB)

[LucaB72]

Hi
i done very bruttaly something similar while working with a Bill Of Quantities (BOQ) for construction.
I crated a table with the various levels of the BOQ itmes, for yoru case Accounts, so I could run the analysis and cut at the desired level.

A more advance solution can be found also in teh enterprise DNA courses, under the Financial Report course -> Balance Sheet and “Managing data at different granularities”

[Melissa]

Hi @pedroccamara,

It could probably do with some more tweaking but should get you started…

let
    Source = Excel.CurrentWorkbook(){[Name="Accounts"]}[Content],
    ChType = Table.TransformColumnTypes(Source,{{"AccountID", Int64.Type}, {"Description", type text}, {"AccountType", type text}}),
    AddGroupID = Table.AddColumn(ChType, "GroupID", each let myText = Text.From([AccountID]) in if [AccountType] = "M" and not Text.StartsWith(myText, "12") then Number.From( Text.Start(myText, Text.Length(myText)-1)) else if [AccountType] = "M" and Text.StartsWith(myText, "12") then Number.From( Text.Start(myText, Text.Length(myText)-2)) else null),
    MergeMovements = Table.NestedJoin(AddGroupID, {"AccountID"}, Movements, {"AccountMovID"}, "Movements", JoinKind.LeftOuter),
    ExpandMovements = Table.ExpandTableColumn(MergeMovements, "Movements", {"Debit", "Credit", "Liquid"}, {"Debit", "Credit", "Liquid"}),
    GroupMovements = Table.Group(ExpandMovements, {"AccountID", "Description", "AccountType", "GroupID"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}, {"Liquid", each List.Sum([Liquid]), type nullable number}}),
    SelfMerge = Table.NestedJoin(GroupMovements, {"AccountID"}, GroupMovements, {"GroupID"}, "Changed Type1", JoinKind.LeftOuter),
    AggregateValues = Table.AggregateTableColumn(SelfMerge, "Changed Type1", {{"Debit", List.Sum, "Sum of Debit"}, {"Credit", List.Sum, "Sum of Credit"}, {"Liquid", List.Sum, "Sum of Liquid"}}),
    UnpivotOtherColumns = Table.UnpivotOtherColumns(AggregateValues, {"AccountID", "Description", "AccountType", "GroupID"}, "Attribute", "Value"),
    ExtractValuesHeader = Table.TransformColumns(UnpivotOtherColumns, {{"Attribute", each Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    PivotBack = Table.Pivot(ExtractValuesHeader, List.Distinct(ExtractValuesHeader[Attribute]), "Attribute", "Value", List.Sum),
    CleanUpColumns = Table.RemoveColumns(PivotBack,{"GroupID"}),
    #"Changed Type" = Table.TransformColumnTypes(CleanUpColumns,{{"Debit", type number}, {"Credit", type number}, {"Liquid", type number}})
in
    #"Changed Type"  

Here’s your sample file. account resume.xlsx (143.1 KB)
I hope this is helpful

[pedroccamara]

Hello @Melissa!!
Your idea is brilliant and i understand most of it, except 2 lines: the AddGroupID (fantastic!) and the SelfMerge, i didn’t know that you could merge in the same query…awesome!
Could you explain me how you would do it if you have a much larger table of account id? Also, i didn’t mentioned but both tables with accountid are text values. Does it matter? Would it be helpful if you already had a parent column in the table of accounts? Different approach?
Let me attach a sample of a larger file for you to know what i mean.
Thanks a lot @Melissa
account resume.xlsx (131.7 KB)

[Melissa]

Hi @pedroccamara,

Okay with the Parent in play I think I can create a much more efficient query
I’ll get back to you.

[pedroccamara]

I love efficiency although sometimes i don’t know how…
Here’s how i did the parent column on my general table of accounts,

BufferMaterial = List.Buffer(#“Changed Type”[AccountID]),
AddParent = Table.AddColumn(#“Changed Type”,“Parent”,(row)=> List.Last(List.Select(List.Transform(BufferMaterial, (trans)=> if trans = Text.Start(row[AccountID], Text.Length(trans)) and trans<> row[AccountID] then trans else null),each _<> null))),

[Melissa]

Hi @pedroccamara,

It’s just the oddest thing - I threw a bunch of M at this but the mainly UI built solution outperforms them all LOL So here’s the full Query again. Note that with the Unpivot/Pivot all records with nulls are automatically removed - if that is undesired - add a step replacing all null with 0 BEFORE the step UnpivotOthers

 let
    Source = Excel.CurrentWorkbook(){[Name="Movements"]}[Content],
    #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"Account ID", Int64.Type}, {"Debit", type number}, {"Credit", type number}, {"Liquid", type number}})),
    GroupAccountID = Table.Group(#"Changed Type", {"Account ID"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}, {"Liquid", each List.Sum([Liquid]), type nullable number}}),
    MergeTOA = Table.NestedJoin(GroupAccountID, {"Account ID"}, #"Table of Accounts", {"Account ID"}, "TOA (2)", JoinKind.LeftOuter),
    ChildLevel = Table.Buffer( Table.ExpandTableColumn(MergeTOA, "TOA (2)", {"Parent"}, {"Parent"})),
    ParentLevel = Table.Buffer( Table.Group(ChildLevel, {"Parent"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}, {"Liquid", each List.Sum([Liquid]), type nullable number}})),
    SartWithTOA = #"Table of Accounts",
    MergeChildren = Table.NestedJoin(SartWithTOA, {"Account ID"}, ChildLevel, {"Account ID"}, "Temp", JoinKind.LeftOuter),
    ExpandChildren = Table.ExpandTableColumn(MergeChildren, "Temp", {"Credit", "Debit", "Liquid"}, {"Credit", "Debit", "Liquid"}),
    MergeParent = Table.NestedJoin(ExpandChildren, {"Account ID"}, ParentLevel, {"Parent"}, "Temp", JoinKind.LeftOuter),
    ExpandParent = Table.ExpandTableColumn(MergeParent, "Temp", {"Debit", "Credit", "Liquid"}, {"Debit.1", "Credit.1", "Liquid.1"}),
    UnpivotOthers = Table.UnpivotOtherColumns(ExpandParent, {"Account ID", "AccountType", "Parent"}, "Attribute", "Value"),
    ExtractHeaders = Table.TransformColumns(UnpivotOthers, {{"Attribute", each Text.BeforeDelimiter(_, "."), type text}}),
    PivotBack = Table.Pivot(ExtractHeaders, List.Distinct(ExtractHeaders[Attribute]), "Attribute", "Value", List.Sum)
in
    PivotBack

Here’s a snapshot

and your sample file. account resume (1).xlsx (255.3 KB)
I hope this is helpful.

[pedroccamara]

Hey @Melissa
Again, i am very sorry cose i don’t think i’ve told you the whole “story”. So sorry.
I believe you’re almost there at the point that i need. I haven’t told you but those accounts with AccountType “M” should sum to the “I” type or “R” if there’s no “I”. Let me explain what i have here:

The hierarchy of account 1421901 are 1421, 142 and 14. I have to see in all those parents account the sum of all 1421* like in pic 3.
Another example with the 21’s. If i have values on the accounts 21111 0015, 21111 0016 and so on, i should have their sum on their parents, “I” (2111, 2111, 211) and the “R” (21), should sum the “I” accountType.
I’m so sorry @Melissa. Will you still be able to help me? Hope so.
Thanks a lot!

[Melissa]

Well we must be getting closer… So by the looks of it all Parents fold to the top level.
See what you make of this.

let
    Source = Excel.CurrentWorkbook(){[Name="Movements"]}[Content],
    #"Changed Type" = Table.Buffer(Table.TransformColumnTypes(Source,{{"Account ID", Int64.Type}, {"Debit", type number}, {"Credit", type number}, {"Liquid", type number}})),
    GroupAccountID = Table.Group(#"Changed Type", {"Account ID"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}, {"Liquid", each List.Sum([Liquid]), type nullable number}}),
    MergeTOA = Table.NestedJoin(GroupAccountID, {"Account ID"}, #"Table of Accounts", {"Account ID"}, "Temp", JoinKind.LeftOuter),
    ChildLevel = Table.Buffer( Table.ExpandTableColumn(MergeTOA, "Temp", {"Parent"}, {"Parent"})),
    AddTopParentChild = Table.AddColumn(ChildLevel, "TopParent", each Number.From( Text.Start( Text.From([Account ID]), 2)), type number ),
    TopParentLevel = Table.Buffer( Table.Group(AddTopParentChild, {"TopParent"}, {{"Debit", each List.Sum([Debit]), type nullable number}, {"Credit", each List.Sum([Credit]), type nullable number}, {"Liquid", each List.Sum([Liquid]), type nullable number}})),
    SartWithTOA = #"Table of Accounts",
    AddTopParent = Table.AddColumn(SartWithTOA, "TopParent", each if [AccountType] <> "M" then Number.From( Text.Start( Text.From([Account ID]), 2)) else null, type number ),
    MergeChildren = Table.NestedJoin(AddTopParent, {"Account ID"}, AddTopParentChild, {"Account ID"}, "Temp", JoinKind.LeftOuter),
    ExpandChildren = Table.ExpandTableColumn(MergeChildren, "Temp", {"Credit", "Debit", "Liquid"}, {"Credit", "Debit", "Liquid"}),
    MergeParent = Table.NestedJoin(ExpandChildren, {"TopParent"}, TopParentLevel, {"TopParent"}, "Temp", JoinKind.LeftOuter),
    ExpandParent = Table.ExpandTableColumn(MergeParent, "Temp", {"Debit", "Credit", "Liquid"}, {"Debit.1", "Credit.1", "Liquid.1"}),
    UnpivotOthers = Table.UnpivotOtherColumns(ExpandParent, {"Account ID", "AccountType", "Parent", "TopParent"}, "Attribute", "Value"),
    ExtractHeaders = Table.TransformColumns(UnpivotOthers, {{"Attribute", each Text.BeforeDelimiter(_, "."), type text}}),
    RemoveColumns = Table.RemoveColumns(ExtractHeaders,{"TopParent"}),
    PivotBack = Table.Pivot(RemoveColumns, List.Distinct(RemoveColumns[Attribute]), "Attribute", "Value", List.Sum)
in
    PivotBack

.

Filtered snapshot

Here’s the updated file: account resume (1).xlsx (164.5 KB)

[pedroccamara]

Hello @Melissa
Yes, like you said, you’re getting there. But, let me tell you why you’re not there yet:

If account id = 14211 or 14212 then sum to next parent account (1421) both values. Which means 3850*2=7700. That doesn’t happen and i believe with other accounts also doesn’t happen. That value on the account 14212 is not correct. It should be 0.
The same thing happen with account143, which is not the sum of 1431 and 1432, you know.
Thanks a lot Melissa

[Melissa]

Ok so do me a big favour and create a full mock-up of the disired outcome in XLS. That way I can validate the numbers when I go over it one more time.

Thanks

[pedroccamara]

Hello @Melissa
You’re the one doing me a big favour. Of course i would do it, and now with all values for each account (group 6).
First sheet with the desire result. Second with only movements and the third sheet the hierarchy parents for each account.
I’m very sorry for the delay of sending you the file.
Thanks a lot @Melissa
account resume.xlsx (83.9 KB)

[Melissa]

Hi @pedroccamara,

I’m really close but unable to provide a full solution.

That’s because there’s an issue with the Type/Parent assignment. See the example below where the Parent of a Type I is of Type M so it’s circling back, moving against the stream.

When you’ve sorted these type mismatches, you can re-use the patterns in the attached file to get to the desired result. account resume (2).xlsx (93 KB)

I hope this is helpful.

[pedroccamara]

Hello @Melissa
I’m glad you’ve found that error on my account list. It took a while to solve it but it’s done.
Thank you very very much for this solution!!! Great idea!!
Best regards