Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pal95
Helper III
Helper III

Count multiple strings in a column

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

2 ACCEPTED SOLUTIONS
Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

View solution in original post

ryan_mayu
Super User
Super User

@pal95

in the pq, split column

1.PNG

then group by

2.PNG3.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

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 ()
    )

1.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ryan_mayu
Super User
Super User

@pal95

in the pq, split column

1.PNG

then group by

2.PNG3.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

amitchandak
Super User
Super User

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.