Cumulative sumproduct in relational data table

tonie_tollig Stachu

I believe there should be a more elegant solution

but this might work as well

The idea is to mege Child ID with Parent ID to get Grand Children and repeat this merge until there are no children other than "NC 1" and NC 2"

Please see the attached file as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMFbSAVGGQMrQRClWB1nQCCRoCRM0QRI0RRMEaTA0QxMEmQnXbYqk2xxN0BiLQhOog2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Component ID" = _t, #"Component ID 2" = _t, Factor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Component ID", type text}, {"Component ID 2", type text}, {"Factor", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Component ID 2"},#"Changed Type",{"Component ID"},"Changed Type",JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Component ID 2", "Factor"}, {"Component ID 3", "Factor3"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Changed Type",{{"Component ID", Order.Ascending}}),
    #"Merged Queries1" = Table.NestedJoin(#"Sorted Rows",{"Component ID 3"},#"Sorted Rows",{"Component ID"},"Sorted Rows",JoinKind.LeftOuter),
    #"Expanded Sorted Rows" = Table.ExpandTableColumn(#"Merged Queries1", "Sorted Rows", {"Component ID 2", "Factor"}, {"Component ID 4", "Factor4"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Sorted Rows",{{"Factor", "Factor2"}}),
    #"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"Component ID 2", "Component ID 3", "Component ID 4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Factor2", type text}, {"Factor3", type text}, {"Factor4", type text}}, "en-US"),{"Factor2", "Factor3", "Factor4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),
    #"Sorted Rows1" = Table.Sort(#"Merged Columns1",{{"Merged", Order.Ascending}, {"Merged.1", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Custom", each List.RemoveItems(Text.Split([Merged],","),{" ",""})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Value.FromText(List.RemoveItems(Text.Split([Merged.1],","),{" ",""}))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Accumulate([Custom.1],1,(state, current) => Number.From(state) * Number.From(current))),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Custom.2", "Factor product"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns1", "Custom.2", each List.Intersect({[Custom],{"NC 1","NC 2"}})),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom3", {"Custom.2", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Merged", "Merged.1", "Custom", "Custom.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Component ID", "Custom.2", "Factor product"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns",{{"Custom.2", "Grand Children"}})
in
    #"Renamed Columns2"

Hello tonie_tollig

You can use this M solution which uses a recursive function...Hopefully it will work with your entire list.

Please see attached file as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nNWMFbSAVGGQMrQRClWB1nQCCRoCRM0QRI0RRMEaTA0QxMEmQnXbYqk2xxN0BiLQhOog2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Component ID" = _t, #"Component ID 2" = _t, Factor = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Component ID", type text}, {"Component ID 2", type text}, {"Factor", Int64.Type}}),
    mytable=Table.Buffer(ChangedType),
    mygrandchildren={"NC 1","NC 2"},
    otherchildren=List.Distinct(List.RemoveItems(mytable[Component ID 2],mygrandchildren)),
    myfunction=(SourceTable)=>
    let
    Mvar= each  + 1,
    myoutput=Table.NestedJoin(SourceTable,{"Component ID 2"},SourceTable,{"Component ID"},"Step",JoinKind.LeftOuter),
    myexpand=Table.ExpandTableColumn(myoutput, "Step", {"Component ID 2", "Factor"}, {"Grand Children", "Factor2"}),
    myexpand1=Table.AddColumn(myexpand, "Factor3", each [Factor] * (if [Factor2]=null then 1 else [Factor2]), type number),
    myexpand2=Table.AddColumn(Table.FromColumns({myexpand1[Component ID],myexpand1[Grand Children],myexpand1[Factor3],myexpand1[Component ID 2]},{"Component ID","Component ID 2","Factor","Component ID s"}),"GC 1",each if [Component ID 2]=null then [Component ID s] else [Component ID 2]),
    myexpand3=Table.FromColumns({myexpand2[Component ID],myexpand2[GC 1],myexpand2[Factor]},{"Component ID","Component ID 2","Factor"})
    in
if List.ContainsAny(myexpand3[Component ID 2],otherchildren) then @ myfunction(myexpand3) else myexpand3,
    FinalTable=myfunction(mytable),
    #"Sorted Rows" = Table.Sort(FinalTable,{{"Component ID", Order.Ascending}, {"Factor", Order.Ascending}})
in
    #"Sorted Rows"

Thank you very much. I will test this!