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 😊.

Regards

 

 

 

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Poste", Int64.Type}, {"Amount", Int64.Type}}),
    #"Added Custom" = Table.Group(#"Changed Type",{"Order"},{{"Sum", each List.Sum([Amount]), type nullable number}, {"Detail", each _, type table [Order=nullable number, Poste=nullable number, Amount=nullable number]}}),
    #"Expanded Detail" = Table.ExpandTableColumn(#"Added Custom", "Detail", {"Poste", "Amount"}, {"Poste", "Amount"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Detail", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Order"}, {"Order.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Added Index1", "Custom", each if [Order] <> [Order.1] then [Index.1] else null),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Total", each if [Order.1] = [Order] then null else [Sum]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Order", "Poste", "Amount", "Total"})
in
    #"Removed Other Columns"

 

 

 

 

Order Poste Amount

8900012  135
8900019  122
8900019  2-44
8900019  313
8900025  27
8900025  315
8900017  143
8900017  3-8
8900017  416
8900017  54
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:

     

6 Replies

  • jennratten's avatar
    jennratten
    Super User
    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

      jennratten ,

      Wonderful, thank you very much, you have all helped me very well,
      I think that's enough for today ğŸ˜Š

      Have a nice evening to all,

      Regards

  • Mederic's avatar
    Mederic
    Post Patron
    3 years ago

    jennratten

    Sorry, I tried your code on my file of 2000 rows and about ten columns, it takes very long time.
    I give up after 15 min and I don't know how to place List.Buffer() in this code

    Maybe this would help to reduce the time considerably !!

    I've done several tests but with no result

    Thanks in advance

    Regards

     

  • Mederic's avatar
    Mederic
    Post Patron
    3 years ago

    jennratten ,

    I just thought of adding an Index column before your step.
    I sorted the Index column and put it under Table.Buffer()
    This solves the slowness problem, it takes less than 10 seconds and your code does the job very well😊,

    Sorry for the inconvenience, but if there is an easier way, I'll take it 

    Thanks again

    Regards

  • 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