Word Cloud - Stop words

[mpfraser7]

Hello,

The word cloud visual has a built in stop words feature but unfortunately, it has a character limit. I have over 100 stop words and I can't fit them all in the stop words feature.

I tried using the Exclude feature of the visual while referencing another table but I can't get it to work. I am trying to visualize the most common words from paragraphs while excluding certain words. 

Here's a sample of the data that I want to use the visual for, it's in a sentence form:

The stop words would't be in a sentence form, they are simple words such as 'Hello'.

Does anyone have an idea on how I can create my own list of stop words and use it as the Exclude field of the word cloud visual?

[v-lionel-msft]

Hi @mpfraser7 ,

If @sturlaws 's answer can solve your problem, then please consider Accept it as the solution to help the other members find it more quickly.

If not, please let us know.

Best Regards,
Lionel Chen

[sturlaws · Accepted solution]

Hi, @mpfraser7,

you can make use of Power Query(Edit queries) in Power BI.

First split your column 1 by space, make sure you expand the Advanced Options,  and check the "Select into Rows". Then do the same for ".", "," and "?". 

Filter out all blank rows from your column. Each row will now be on a separate word.

Now create a table with your stop words, e.g. create it in excel and import it, or by the "Enter data" functionality in Power BI/Power Query.

Now make use of the Merge Queries-function in the Home-ribbon, and merge the to tables. The rows where there is a match between the two table will have a value, the others will be null. To make it easier to filter, add a new custom column like this:

=if [Stop words.words] is null then 1 else 0

and filter the table on this column = 1. After this filtering you can remove the merge column and the added column. Then only words which are not stop words will be loaded to your model.

mockup-report

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Mockup