Forum Discussion
Word cloud visual how to use the Exclude feature?
Hi
I'm trying do do the same thing as you, I want to clean my wordcloud providing several stopwords. I do not want to use the copy paste function but rather the exclude function.
As for now power bi does not recognize the relationship and I can't make it work. The lenght of the sopword column are not the same as the column that contains the text.
Do you have any example file or suggestions on how to do make the stopwords work in worudcloud/power bi?
Here's my attempt:
1. Take a table (I used the Table Capture add-on from Chrome, but you could use Get Data from Web in Power BI) from Wikipedia to generate a Dimension table of the 100 most common words in English. https://en.wikipedia.org/wiki/Most_common_words_in_English
DimCommonEngWords
2. Remove all columns except "Word" column.
3. Add index to Word column to number rows from 1-100.
4. Pivot your Word column by the Index column so that you get 100 columns with Index (1-100) as the column name and Word as the value.
Pivoted Word column
5. Merge all 100 columns and separate by a delimiter (e.g., a comma).
6. Add a new custom column in both the DimCommonEngWords and FactTable (Exclude Words with a value of 1 for all rows) that will connect the two queries.
Custom column - Exclude words
7. Merge queries to add the Merged words column to your fact table.
8. Split Merged words column by delimiter
9. Unpivot split columns into 1 column called "Word to exclude".
Word to exclude
10. Close and apply.
11. Add "Word to exclude" column to Excludes. Note: you will have to update any measures to account for the 100 rows you just added to each comment.
Word cloud options