Forum Discussion
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...
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 CustomRESULT:
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
CustomRESULT:
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!