Forum Discussion
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 help me please ?
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:
3 Replies
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.
Hello again - you would just need to modify the second line highlighted below with the logic that should be used to determine which rows should be summed. The first highlighted line below is just a comment to help you,
Then you can decide what to do with the additional rows.
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 // Add a new column with the logic which identifies if the row should be excluded/included if [Request_Type] = "400x" or [Request_Type] = "500x" 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"
