Word cloud visual how to use the Exclude feature?

So if you go back to Lazer's question. How are the keys 1 and 2 related to each other?

If I have a table with 1000 long form answers (sentences), and I create a table with stopwords. How do I setup key columns to link the two tables? The table inherently don't have any relationship to each other.

Hello

i saw your answer and im trying to do the same, is there any way i can see the queries that form that visualization in power bi

Thanks for the example. But could you elaborate on the role of the key column between the two tables?

For example, the Words dataset includes the words "Power BI" and "Microsoft" with keys 1 and 2 respectively. The Excludes dataset includes the words "Javascript" and "LESS" with keys 1 and 2. I would expect that the exclusion word "Javascript" is applied to "Power BI" and "LESS" is applied to "Microsoft". In that case, the exclusion list should have no effect on the word cloud. However, every word in the exclusion list seems to be applied to every word in the target dataset.

It is really hard to grasp the meaning of the key column. Although it is required, it seems that it has no effect on how the word cloud is presented? Why is it required?

Yes, I understand that. Thanks.

Can you give more insight into this question from above? "If I have a table with 1000 long form answers (sentences), and I create a table with stopwords. How do I setup key columns to link the two tables? The table inherently don't have any relationship to each other."

Thanks,

v-viig, that did the trick for me also, but it's not obvious and there are no clear instructions when downloading the visual.  I have just left a review on AppSource saying how clunky it is and whilst your answer may add a star to my rating I still hold that it's not intuitive - it would be much better just to add a list of words stright to the Excludes field without extra index fields and a relationship being needed. 

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?

Do you use different tables? If so, please learn how to make a relationship between two table otherwise PBI won't be able to use these tables together.

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

[email protected]

Key column is required to make a relation for two or more tables. This requirement comes from from how SQL handles tables.

In other words, table must have primary kyes that might be use to join tables.

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

[email protected]

Both table must have a Key (call it as you want) column that containse excluding words.

Rows will be contcatenated by key value.

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

[email protected]

It does not matter how  mmany words are in the tables. Tables must have key column with numeric indexes.

Tables should also have a relationship in Power BI that is built on the key column. This is a  small hack to use both tables  in a  single visual.

Ignat Vilesov,

Software Engineer

Microsoft Power BI Custom Visuals

[email protected]

Hi v-viig , thanks for the trick which works well in my case.
However one drawback remains : I cannot use the other filters I am using in my report as when triggered some of my exclusion words are then not taken into account anymore in the word cloud viz. Any suggestions on this issue ?

Hi,

Could you provide more details and some screens regarding your issue?

Kind Regards,

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
[email protected]

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

Hi jason435 

Could you solve your problem in the meantime?

I have absolutly the same problem here and would be very grateful 🙂

Cheers

qwertzuiop