Forum Discussion

u4us1923's avatar
u4us1923
Frequent Visitor
2 years ago
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...
General Comment
Need Help
  • jennratten's avatar
    jennratten
    2 years ago

    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:

     

u4us1923's avatar
u4us1923
Frequent Visitor
2 years ago

 Thank you. It's worked.

 

I'm just wondering what to do if I only want to sum the 400x and 500x.

jennratten's avatar
jennratten
Super User
2 years ago

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"