Forum Discussion

Mederic's avatar
Mederic
Post Patron
3 years ago
Solved

Optimising code : Total on last order rows

Hello everyone, I have another request please. I have managed to get what I want but I find that my code has too many steps. Can I get the same result with only 2 or 3 steps? Thank you in advance...
Need Help
  • jennratten's avatar
    jennratten
    3 years ago

    Hello - this is how you can get the same result in less steps:

    SCRIPT:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc1LCsAgDEXRvWSsEPOpuhZx/9toIyktb5DJyYW3Fo3JzE2oUHtOnXZ5cSaKAEZdzUA1cv1QPNMOdsL/UM8hU8Ao6wC0qC9AP4+9bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Poste = _t, Amount = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Poste", Int64.Type}, {"Amount", Int64.Type}}),
    Custom = Table.AddColumn ( ChangeType, "Custom", each 
    let 
        t = Table.SelectRows(ChangeType, (x)=> x[Order]=[Order]),
        varSum = List.Sum ( t[Amount] ),
        varMax = List.Max ( t[Poste] )
    in
    if [Poste] = varMax then varSum else null
    )
in 
    Custom

    RESULT:

     

Mederic's avatar
Mederic
Post Patron
3 years ago

Hello everyone, hello jennratten ,
Sorry, I tried to integrate 2 more columns, Index and Table.Buffer but it still takes a long time to update.

Maybe I didn't complete the code properly ?
How can I speed up the code for method 2 ?

Thanks in advance

Regards

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Poste", Int64.Type}, {"Amount", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(ChangeType, "Index", 1, 1, Int64.Type),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Index",{{"Index", Order.Ascending}})),
    Custom = Table.AddColumn ( #"Sorted Rows", "Custom", each 
    let 
        t = Table.SelectRows(#"Sorted Rows", (x)=> x[Order]=[Order]),
        varSum = List.Sum ( t[Amount] ),
        varMax = List.Max ( t[Poste] )
    in
    if [Poste] = varMax then varSum else null
    )
in 
    Custom