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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.