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
Anonymous
Not applicable

Count frequency of words in a column

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?

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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

wo.png

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

Capturexx.PNG

Sort the Count column is Descending order.

Regards

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

6 REPLIES 6
PhilipTreacy
Super User
Super User

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

wo.png

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

Capturexx.PNG

Sort the Count column is Descending order.

Regards

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thanks Man it really helped!

Anonymous
Not applicable

@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
Not applicable

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

datafield_0-1610570777271.png

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
Super User
Super User

@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
Not applicable

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?

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.