Get sums and min date recursive

[autotest100]

Hi

I have a similar task, as this https://community.fabric.microsoft.com/t5/Power-Query/How-to-calculate-sums-and-first-sales-dates-in....

Now I have this table:

And I need this result table:

How can I get it? I've attached the testile: Test-Successors.pbix

Best regards

Stefan

[autotest100 · Accepted solution]

Thanks to all. This function solved my problem: Guest Post: Using List.Accumulate for Input/Output Genealogy – (thebiccountant.com)

[Anonymous]

Hi @autotest100 

You can create a blank query and put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY/BDgIhDER/xXBeUzoFdvdbCCe9a4z+v6WrNCyntszrTGsNHPVxWMLz9bh/bu8LX3tnY2iByDsxrVruJbSl+pcT+BNiBEBC3IcijsiE9O4QCxVC6hsQTQ0+RYJFOsJsunozO9di0lrqOf9K6H6SnJGJkcH8LoikRlqW4kiakDSQdCBMIDZcnMkTk8fRoEzobUJo7Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Id" = _t, Product = _t, #"Successor Id" = _t, #"First salesdate" = _t, #"Quantity sold" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Id", Int64.Type}, {"Product", type text}, {"Successor Id", Int64.Type}, {"First salesdate", type date}, {"Quantity sold", Int64.Type}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Product", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Product.1", "Product.2", "Product.3"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition","-","",Replacer.ReplaceText,{"Product.2"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Sum", each let a=[Product.2],
b=[Product Id]
in List.Sum(Table.SelectRows(#"Replaced Value",each [Product.2]=a and [Product Id]<=b)[Quantity sold])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Mindate", each let a=[Product.2],
b=[Product Id]
in List.Min(Table.SelectRows(#"Replaced Value",each [Product.2]=a and [Product Id]<=b)[First salesdate]))
in
    #"Added Custom1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise]

See this video

https://youtu.be/h5HE1FtAyGc?si=7qzuYbffkpHU-wtq