Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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
Proud to be a 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
You can then Group By the words and choose Count as the aggregation.
Sort the Count column is Descending order.
Regards
Phil
Proud to be a Super User!
Thanks Man it really helped!
@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.
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
@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/
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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
108 | |
106 | |
93 | |
69 |
User | Count |
---|---|
167 | |
132 | |
130 | |
96 | |
91 |