How can I group rows based on certain special characters

[Anonymous]

For example here is a chart

    Category               Value

   razor                         10

   disposable razor       11

   shave                         0

   razor for men            2

   shampoo                   5

   razors                       21

I want to group by rows with the key word razor, even if the colomn name is not distinctly 'razors'. The calculated value in this example would be 10+11+2+21 = 44

[tackytechtom · Accepted solution]

Hi @Anonymous ,

How about this:

And here the measure:

RazorMeasure = 
CALCULATE (
    SUM ( Table[Value] ),
    SEARCH ( "Razor", Table[Category], 1, BLANK() )
)

Does this solve your issue?

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[jennratten]

This is how you can accomplish the result using Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkqsyi9S0lEyNFCK1YlWSsksLsgvTkzKSVWAyxiCZYozEstSgVyIOrCkQhoQ56bmAUWNYGpyC/LzgXxThKpikDTQjFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Contains Keyword", each Text.Contains ( [Category], "razor", Comparer.OrdinalIgnoreCase ), type logical ),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Contains Keyword"}, {{"Sum", each List.Sum([Value]), type nullable text}})
in
    #"Grouped Rows"

[tackytechtom · Accepted solution]

Hi @Anonymous ,

How about this:

And here the measure:

RazorMeasure = 
CALCULATE (
    SUM ( Table[Value] ),
    SEARCH ( "Razor", Table[Category], 1, BLANK() )
)

Does this solve your issue?

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous]

Do you by chance know how to do it with two searches? For example, "razor" and "shampoo" to make the razorshampoo measure as 49?

[tackytechtom · Accepted solution]

Hi @Anonymous ,

How about this:

RazorMeasure2 = 
CALCULATE (
    SUM ( Table[Value] ),
    SEARCH ( "Razor", Table[Category], 1, BLANK() ) || SEARCH ( "Shampoo", Table[Category], 1, BLANK() )
)

Please consider marking this reply as a solution, if it solves your issue. 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/