Count substrings in comma-separated string of varying length

[TMielke]

Hello,

I have a special problem which can be simplified to a table like this:

Number of Items	Items	Desired measure
1	Apple_xxyyy 4	1
3	Banana_xxyxy 2, Melon_xxxyy 3, Orange_xxy 6	0
5	Banana_xxyyx 2, Apple_yyyy 2, Apple_xxxx 3, Apple_yxyx 5,  Orange_yxyx 1	3

Now what I need is to count the occurence of special substrings (let's say "Apple_" for example) considering the comma delimiters. So within each comma delimited substring, I need to do a check (e.g. contains "Apple_") if true, sum it up. The problem is, that the number of items can be huge (let's say up to 500). How can I do this? I would prefer a solution without having to split the original column, however if there's no other way, it could be done.

Thanks a lot in advance!

[Hema_Gupta]

Hi,

Hope this helps.

[danextian]

Hi @TMielke ,

Assuming that you have a column that indicates what text to find and that for each delimiter two instances of the text to find is to be counted just once  (say Apple_xxxx and apple_yyyy are counted once), this can be fairly done in Power Query. Here's a sample custom column formula

let 
texttofind = Text.Lower([text to find]),
substrings = Text.Split([Items],","),
lowercased = List.Transform(substrings, Text.Lower),
filtered = List.Select(lowercased, each Text.Contains(_, texttofind))
in List.Count(filtered)

here's a sample M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIsKMhJja+oqKysVDAB8kFiiSAxpVidaCVjIM8pMQ8IQUoqKhWMdBR8U3Py84BcoBYFYx0F/6LEvHSwCQpmQNUGQJwLUgHWb4qiv7ICpB9iYyXIQjgPaFoFyDCoHNAqBVOdQwtghoMFQC4zRrguFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Number of Items" = _t, Items = _t, #"Desired measure" = _t, #"text to find" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number of Items", Int64.Type}, {"Items", type text}, {"Desired measure", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Count", each let 
texttofind = Text.Lower([text to find]),
substrings = Text.Split([Items],","),
lowercased = List.Transform(substrings, Text.Lower),
filtered = List.Select(lowercased, each Text.Contains(_, texttofind))
in List.Count(filtered))
in
    #"Added Custom"

[Ahmedx · Accepted solution]

pls try this

[TMielke]

this one seems to work for me exactly as expected! thanks a lot!

[Arul]

@TMielke ,

try this measure,

Count Measure =
LEN ( SUBSTITUTE ( MAX ( 'Table'[Items] ), "Apple_", "Apple_0" ) )
    - LEN ( MAX ( 'Table'[Items] ) )