Count occurrence of all delimited words in single column

[DarylRob]

Hi so i've got one column in my table that contains sector infomation much like the below.

Sectors Covered

Consumer Discretionary;Consumer Discretionary Products;Automotive

Health Care;Health Care Facilities & Svcs

Consumer Services;Insurance;Consumer Discretionary;Financials;Consumer Discretionary Services;Automotive

Consumer Discretionary Products;Consumer Discretionary Services;Retail & Whsle - Discretionary;Consumer Staple Products;Retail - Consumer Staples

I need some osrt of output, like a table that would count all occruances of each in the table

Sector	Count
Consumer Discretionary	3
Consumer Discretionary Products	1
Automotive	2
Financials	1

[v-yanjiang-msft]

Hi @DarylRob ,

According to your description, I create a sample, there're two tables

Table:

Sector table:

Here's my solution, create a measure.

Count =
VAR _T =
    ADDCOLUMNS (
        'Table',
        "Sector",
            IF (
                CONTAINSSTRINGEXACT ( 'Table'[Sectors Covered], MAX ( 'Sector'[Sector] ) ),
                1
            )
    )
RETURN
    SUMX ( _T, [Sector] )

Get the correct result:

Note: In your sample, there're two Consumer Discretionary Products, so the count should be 2.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DarylRob]

This perfect thank you, 

Just on this as well, is there a way to tweak it so instead of counting the number of occurances we could Sum the total of another column in the original table, say if there was a "Revenue" column. We could then sum the revenue for all those that contain a specific sector?

[v-yanjiang-msft · Accepted solution]

Hi @DarylRob ,

According to your description, I modify the sample. There's a Revenue column in the fact table. If rows in the Sector Covered contains any value in the Sector table, sum the Revenue with these rows.

In the sample, you can see , the second row "Health Care;Health Care Facilities & Svcs" doesn't contains values in Sector table, so the result should be 8(1+3+4). Here's my solution, create a measure.

Sum =
VAR _T =
    ADDCOLUMNS (
        'Table',
        "Flag",
            IF (
                COUNTROWS (
                    FILTER (
                        VALUES ( Sector[Sector] ),
                        SEARCH ( 'Sector'[Sector], 'Table'[Sectors Covered],, 0 )
                    )
                ) > 0,
                1
            )
    )
RETURN
    SUMX ( FILTER ( _T, [Flag] = 1 ), [Revenue] )

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hokeson]

you can use split column by delimiter transformation. select the delimiter. here probably semicolon, each occurrence, and in advanced options split to Rows. 

then you get a table of all occurrances.

use matrix visual and to rows put sectors and in values count of sectors.

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVCxCsJADP2V0NlCcXC5SSpFN7GDQ+0QzkAD155ccgX/3kO0SLF0C+/lveS9pslKP0jsKcCBxQZS9gOGp/kPwzn4e7QqZh/V9155pKzdNNmR0GkHJQYyPzNUaNmxMgncYlFsd1CPVt6S6UJNYWRLYk4JCDhYWrhuKh4Szehk6b/Ja/bfWpw1uwspsvtmuHbiCPKlymrFR+In8484h9lG6qF9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sectors Covered" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sectors Covered", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Sectors Covered", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sectors Covered"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sectors Covered", type text}})
in
    #"Changed Type1"