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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
sarah2
Frequent Visitor

Counting multiple words (in a cell) over multiple columns

I have a data set using google forms to track where each person is located on a day. 

sarah2_0-1669996144412.png

I need to be able to track how often someone is at each location using a slicer for date and another for person (a graph that has a bar for each location so I can set the slicer to just Jan 2 and 3 and set it to only be looking at Arden and have a product that on the x axis says Location A with bar=1, Location B with a bar=0, Location C with a bar=1.) It could also be a few Cards that have the number for each location (connected to the slicer for time and person)

There is a consistent list of 19 people that I could list out into another tab in excel if that is helpful

 

I also need to be able to add Location A and B to get a total number of people at those locations with a slicer for time (I can do this in excel, but I was curious if power bi could count names separated by a comma?) This might also look good as a line graph with a line for each location (x being time, y being number of people)

I've read a lot of responses on this page and I'm so thankful for all your questions and responses! This one stumped me in its current configuration so I was hoping someone had an idea. Thank you for your time!

1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

You use Power Query to transform your data.

Consider the dataset

jgeddes_0-1670000177919.png

select the three location columns and under the Transform tab select Unpivot Columns, Unpivot Only Selected Columns

jgeddes_1-1670000231285.png

you should end up with something like

jgeddes_2-1670000281177.png

right click on the value column and select Split Column, By Delimiter

jgeddes_3-1670000332274.png

split by comma and under the Advanced options choose Split into Rows

jgeddes_4-1670000387296.png

Trim the resulting value column (Transform -> Format -> Trim)

Filter out blanks from the value column

You can now Close & Apply

You can now create the visuals you mentioned, for example...

jgeddes_5-1670000589409.png

Hope this gets you going in the right direction.

 

 




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

Proud to be a Super User!





View solution in original post

1 REPLY 1
jgeddes
Super User
Super User

You use Power Query to transform your data.

Consider the dataset

jgeddes_0-1670000177919.png

select the three location columns and under the Transform tab select Unpivot Columns, Unpivot Only Selected Columns

jgeddes_1-1670000231285.png

you should end up with something like

jgeddes_2-1670000281177.png

right click on the value column and select Split Column, By Delimiter

jgeddes_3-1670000332274.png

split by comma and under the Advanced options choose Split into Rows

jgeddes_4-1670000387296.png

Trim the resulting value column (Transform -> Format -> Trim)

Filter out blanks from the value column

You can now Close & Apply

You can now create the visuals you mentioned, for example...

jgeddes_5-1670000589409.png

Hope this gets you going in the right direction.

 

 




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

Proud to be a Super User!





Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors