Count frequency of words in a column

[Anonymous]

Hi

I couldn't find a solution for this calculation. I have one column with product titles as values. Now I have to count the frequency of repeating words. It can be limited to the top 100 repeated words. Every month new product titles are added with new or existing words. Therefore the output has to be dynamic. The product titles are all different. Example:

TITLE

Good bad house dog One

one dog bad One

car good bad good water

So in this case the output would be:

Good   3

bad   3

dog 2

One   2

house 1

car   1

water   1

one   1

How can I count the most occuring words in a column?

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download this PBIX file with an example solution.

In Power Query split the column by delimiter (space) and choose to split into rows

You can then Group By the words and choose Count as the aggregation.

Sort the Count column is Descending order.

Regards

Phil

[razi2905]

Thanks Man it really helped!

[Anonymous]

@PhilipTreacy wow, appreciate your effort to help and the solution you created, thank you! This would work yes. Unfortunately the sum of words in titles in the future will be in million range. So the solution with a column for words would cause performance issues. I'll try the word cloud and see how this works. But at least your solution could help in a future smaller case.

[Anonymous]

Just saw that there is this great word cloud visual. This is almost what I search for:

As you can see it counts the most frequent words. For example "1" occurs 861 times. Now what I finally need is a list of this output instead of the cloud with a visual of two columns/measures:

WORD | AMOUNT

[amitchandak]

@Anonymous , There is an option to split by delimiter(into rows) in power bi, See if you can use that.

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

[Anonymous]

Hi

Splitting the product title column would result in infinite column splitting (as the values of the column have an infinite combination of words).

Simply put, Power BI has to make a list with the most occuring words of a column. Power BI has to search by itself which words (top 100 words repeated would be fine). Can you help with this?