Count multiple strings in a column

[pal95]

Hello,
I have a column which can have multiple users; it looks like:

              Column1
Row 1: UserA
Row 2: UserB
Row 3: UserB, UserC, UserG, UserZ
Row 4: User D,
Row 5: UserA, UserD, UserG
Row 6: UserD, UserF

As we see, UserD appeared in the column 3 times, UserA appeared 2 times etc. How to pull out every user from the column to make a visual which says how many times each user appeared in the column?

Thank you for your time!

Regards,
Wojciech

[Pragati11 · Accepted solution]

Hello @pal95 ,

You will need to split your column into multiple columns in the query editor as shown in this blog:

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

You can then count the occurrences of a string in multiple columns that reference the following existing thread:

https://community.powerbi.com/t5/Desktop/Count-instances-of-string-in-multiple-columns/td-p/239302

Thank you

Pragati

[Anonymous]

Hi @pal95,

You can try to use following calculate table formulae to analysis raw table and generated the summarize user tables based on each item and their count:

Summary = 
VAR merge =
    SUBSTITUTE (
        CONCATENATEX ( ALL ( 'Table'[Column1] ), [Column1], "," ),
        " ",
        ""
    )
VAR _path =
    SUBSTITUTE ( merge, ",", "|" )
VAR list =
    ADDCOLUMNS (
        GENERATESERIES ( 1, PATHLENGTH ( _path ), 1 ),
        "Users", PATHITEM ( _path, [Value] )
    )
RETURN
    FILTER (
        GROUPBY ( list, [Users], "Count", COUNTX ( CURRENTGROUP (), [Users] ) ),
        [Users] <> BLANK ()
    )

Regards,

Xiaoxin Sheng

[ryan_mayu · Accepted solution]

@pal95

in the pq, split column

then group by

[Pragati11 · Accepted solution]

Hello @pal95 ,

You will need to split your column into multiple columns in the query editor as shown in this blog:

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

You can then count the occurrences of a string in multiple columns that reference the following existing thread:

https://community.powerbi.com/t5/Desktop/Count-instances-of-string-in-multiple-columns/td-p/239302

Thank you

Pragati

[amitchandak]

@pal95 , see split by row/column in power Query.edit query can help

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/