Forum Discussion
Solved
Summation based on specific data in rows
I have a table like below. This time I need to add 400x,500x,300x and 401x values in the same table and show the result as “error” ? I tried the code above but I was not successful.. 😞 could you hel...
Hello - this will get it done. This adds a custom column which assigns a new name based on whether or not the Request_Type ends in an "x" (case insensitive) and then conditionally groups and sums.
Add a blank query, open the advanced editor and replace the contents with this script:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScxR0lEyNDAwUIrViVYKLk1OTi0uBgpZQkVMDAwqwCrAPFMIzwjCMzYwiADyjGEqDUFyJkBeLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Request_Type = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Request_Type", type text}, {"Count", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.EndsWith ([Request_Type], "x", Comparer.OrdinalIgnoreCase ) then "Error" else [Request_Type], type text), #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"CustomSum", each List.Sum([Count]), type nullable text}},0,(x,y)=> Number.From ( x <> y)) in #"Grouped Rows"Result:
Hello - this will get it done. This adds a custom column which assigns a new name based on whether or not the Request_Type ends in an "x" (case insensitive) and then conditionally groups and sums.
Add a blank query, open the advanced editor and replace the contents with this script:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCskvScxR0lEyNDAwUIrViVYKLk1OTi0uBgpZQkVMDAwqwCrAPFMIzwjCMzYwiADyjGEqDUFyJkBeLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Request_Type = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Request_Type", type text}, {"Count", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.EndsWith ([Request_Type], "x", Comparer.OrdinalIgnoreCase ) then "Error" else [Request_Type], type text),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"CustomSum", each List.Sum([Count]), type nullable text}},0,(x,y)=> Number.From ( x <> y))
in
#"Grouped Rows"
Result:
Thank you. It's worked.
I'm just wondering what to do if I only want to sum the 400x and 500x.