Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mederic
Helper V
Helper V

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

 

Group_Total.jpg

 

 

 

 

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
1 ACCEPTED SOLUTION
jennratten
Super User
Super User

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:

jennratten_0-1679168021558.png

 

View solution in original post

6 REPLIES 6
Mederic
Helper V
Helper V

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

 

 

Subtotal.jpg

Mederic
Helper V
Helper V

@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
Helper V
Helper V

@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

 

jennratten
Super User
Super User

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:

jennratten_0-1679168021558.png

 

@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

You're welcome!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors