cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Patron
Post Patron

Word cloud visual how to use the Exclude feature?

Found the Word Cloud visual which was exactly what I need.

 

But I am failing to understand how to use the Exclude field.

 

Anyone knows how it works?

I am guessing it would be a way to add a DataSource with Stop Words?

WordCloud - Exclude Field.png

 

 

32 REPLIES 32

Hi,

 

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

 

Kind Regards,

 

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com

Frequent Visitor

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?

Community Champion
Community Champion

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

pbicvsupport@microsoft.com

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.

Hi @jason435 

Could you solve your problem in the meantime?

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

 

Cheers

qwertzuiop

Community Champion
Community Champion

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

pbicvsupport@microsoft.com

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,

Community Champion
Community Champion

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

pbicvsupport@microsoft.com

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

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

 

DimCommonEngWordsDimCommonEngWords

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 columnPivoted 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 wordsCustom 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 excludeWord 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 optionsWord cloud options

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

pbicvsupport@microsoft.com

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors