Counting words in a column

[annetoal]

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

[Daviejoe · Accepted solution]

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.

[Fowmy]

@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

[annetoal]

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 · Accepted solution]

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

starting from this data table

you can get this result:

[annetoal]

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

Anne

[annetoal]

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

Thank you

Anne

[Fowmy]

@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

[annetoal]

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

CompanyName	CustomerID	KeyPhrase
Madrigal Electric	90120	fat cat
Madrigal Electric	2003754	big cat
Madrigal Electric	96331023	mouse-killing machine
Madrigal Electric	60010	traffic light
Madrigal Electric	9867623	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]

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?

[annetoal]

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

Thank you,

Anne

[Daviejoe]

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

[Daviejoe · Accepted solution]

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.

[annetoal]

That's it! Thank you so very much.

Anne

[Daviejoe]

Hi Anne,

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

Have a great day.

David

[Daviejoe]

Hi Anne,

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

[annetoal]

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

[Daviejoe]

Create a measure like below

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

[annetoal]

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

[Daviejoe]

Hi Anne,

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

I'll get my thinking cap on!

David