Forum Discussion
Counting words in a column
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.
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:
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
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:
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 🙂
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
