Forum Discussion

autotest100's avatar
autotest100
Frequent Visitor
1 year ago
Solved

Get sums and min date recursive

Hi

 

I have a similar task, as this https://community.fabric.microsoft.com/t5/Power-Query/How-to-calculate-sums-and-first-sales-dates-including-data-of/m-p/4247448#M140334.

 

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

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.