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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
annetoal
Helper II
Helper II

Counting words in a column

A table contains a column with 7188 rows. Each row contains a short phrase or a single word. I want to go through row by row and see how many times a phrase appeared. Like how many times did "house cat" appear, how many times did "fat cat" appear.

 

I have created a column with this formula:

WordCount = COUNTA(TableName[ColumnName])
Then I use the Matrix control putting the column contents in the Rows and the WordCount in the Values.

 

For reasons I don't get, any phrase that just appears once, shows up in the Matrix with a count of 7188. Can someone please help me get this right? 

 

Thanks,

Anne

2 ACCEPTED SOLUTIONS

If you use the key phrase column as your row headers in a matrix and create the below measure

 

Key Phrase Count =
CALCULATE( COUNTROWS( PhraseTable))
 
Put the measure in your matrix and that should be good.




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

Proud to be a Super User!




View solution in original post

Anonymous
Not applicable

I don't know if it is possible also in DAX, but using only a function in PQ,

 

 

starting from this data table

 

image.png

you can get this result:

 

image.png

View solution in original post

18 REPLIES 18
Fowmy
Super User
Super User

@annetoal 

Are you looking for a Power Query or  DAX solution?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

I would prefer DAX if possible. However from reading some of the things on the net I see a lot of people recommending PowerQuery. I am a complete novice at PowerQuery, and slightly less of a novice at DAX. Please, if you could provide a basic solution that a noob can handle, I would be very appreciative.

 

Anne

Anonymous
Not applicable

I don't know if it is possible also in DAX, but using only a function in PQ,

 

 

starting from this data table

 

image.png

you can get this result:

 

image.png

Thank you so much for the Power Query. I will give it a shot the next time I have this assignment.

 

Anne

This is helpful, and it will give me more options for dealing with this next time I have to do it.

 

Thank you

Anne

@annetoal 

You need the have the search words or phrases in a table.

If you have sample data, please share it.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Privacy rules keep me from sharing the actual file, but here is a 5-row example identical to the 7188-row file.

CompanyNameCustomerIDKeyPhrase
Madrigal Electric90120fat cat
Madrigal Electric2003754big cat
Madrigal Electric96331023mouse-killing machine
Madrigal Electric60010traffic light
Madrigal Electric9867623 fat cat

 

It should return a count of 2 for "fat cat" and every other phrase should be counted 1 time. However the way I am doing things right now, it will return a result of 10 for fat cat and every other result will be 5.

Additional information on the original data: 7188 rows, and the word "shopping" appears 5 times. The matrix report in PBI shows it as 35,940 times (7188 x 5). This is what I need help understanding. It's going back through the whole column the number of times it actually appears and multiplying it by the number of rows.

I guess I could fudge it and just divide all results by the number of rows and get the actual figure, but I want to understand the right way to do this.

 

Thanks,

Anne

Anonymous
Not applicable

Hi @annetoal 

 

"

It should return a count of 2 for "fat cat" and every other phrase should be counted 1 time. However the way I am doing things right now, it will return a result of 10 for fat cat and every other result will be 5.

Additional information on the original data: 7188 rows, and the word "shopping" appears 5 times. The matrix report in PBI shows it as 35,940 times (7188 x 5). This is what I need help understanding. It's going back through the whole column the number of times it actually appears and multiplying it by the number of rows.

I guess I could fudge it and just divide all results by the number of rows and get the actual figure, but I want to understand the right way to do this."

 

I don't know DAX and I don't know the formulas you use, but it seems to me, from the examples given, that if you divide the count you do by 5 or, in general, by the number of rows in the table(7188?), you get the result you are looking for.
Isn't that what it is?

 

Seems to be! This was what I was referring to as a "fudge."

 

Thank you,

Anne

Hi @Anonymous 

 

I believe the DAX measure I gave to @annetoal is working for her, it's a pretty simple solution.

 

Appreciate your input and I liked your Power Query suggestion.

 

David





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

Proud to be a Super User!




If you use the key phrase column as your row headers in a matrix and create the below measure

 

Key Phrase Count =
CALCULATE( COUNTROWS( PhraseTable))
 
Put the measure in your matrix and that should be good.




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

Proud to be a Super User!




That's it! Thank you so very much.

 

Anne

Hi Anne,

 

no worries, happy to help once my tired brain understood what you were needing.  

 

Have a great day.

 

David





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

Proud to be a Super User!




Hi Anne,

 

will you only see a distinct phrase in each row or can you have multiple instances of different phrases in one row?





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

Proud to be a Super User!




In this case, each row will only have one phrase. It might be a few words or sometimes just one word: "shipping." 7,188 rows, some with commonly-used phrases, some with one-off phrases, some with one single word. I need to identify the phrases customers mention most.

 

Thank you

Anne

Create a measure like below

 

Key Phrase Count =
CALCULATE (
COUNTROWS ( PhraseTable ),
FILTER ( PhraseTable, CONTAINSSTRING ( PhraseTable[KeyPhrase], "fat cat" ) )
)





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

Proud to be a Super User!




Must I write a FILTER line for each phrase? Because I have thousands of different key phrases. The whole point of this is to try to identify what phrases customers use most. Is there a way to programmatically look at each row and see how many times  the phrase in it appears in the column, without my having to manually enter the phrase?

 

Thank you for staying with me as we work through this--

Anne

Hi Anne,

 

my apologies, I didn't understand the nature of your request.

 

I'll get my thinking cap on!

 

David





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

Proud to be a Super User!




Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors