mssqltips logo

Word Cloud in Power BI Example

By:   |   Updated: 2018-05-01   |   Comments (4)   |   Related: More > Power BI

Problem

Power BI has wide usage in the business intelligence market and offers a powerful solution for business intelligence projects. On the other hand, business or user requirements still continuing to change in business intelligence projects. Assume that you need to visualize the main theme of customer feedback text or the most read news. For this type of analysis, a word cloud is an appropriate solution. How do we use word cloud analysis in Power BI?

Solution

A Word Cloud will visualize the frequency of word for the given text. The word which has the highest frequency is shown larger than others. For this reason, when we want to analyze text or a word string, we can use Word Cloud analysis. The Power BI marketplace provides a word cloud visual and this visual adds the word cloud analysis capability to Power BI. In the Power BI marketplace, we can find these visuals for free and can add them into Power BI.

Now we will demonstrate how to use the word cloud visualization in Power BI.

Word Cloud in Power BI

In the first step of this demonstration we need to connect to the Power BI marketplace and download the word cloud visual. We will open the Power BI Desktop and click import a custom visual.

custom visual

In order to complete this step; click import from marketplace and then connect to the marketplace. 

import from marketplace

When you click Import from marketplace then Power BI asks for account information.

sign in

When we complete these steps, we will connect to the Power BI Visuals marketplace.

power bi visuals

In the Power BI Visuals marketplace, you can find various visual charts and components. Now we will find the word cloud component and will add it to Power BI.

word cloud

Now the word cloud component is ready for use. We can see this component on the right side of the Power BI visualizations tab.

visualizations

In this step we will add a data source to analyze the word cloud visual. In this demonstration we will use a text data source. But you can use another data sources (i.e. SQL Server, Excel, SAP HANA, etc.) which are accessible by Power BI. We will select Get Data and then click Text/CSV in the main design surface of Power BI.

get data

We will select a Notepad document as the data source.

desktop
column

Now our data source is ready to use. In this step we will drag and drop the data source fields to the word cloud component.

Drag and drop Column1 to the Category field.

new text

Our Word Cloud component will appear on the Power BI design surface and will look like this.

marketplace

As a best practice, we don't want to visualize propositions or meaningless words in word cloud analysis. Because they don't define any meaning and can be confusing. For this reason, we have to exclude these values. To perform this step, we will enter some data to Power BI to exclude these words. You will click Enter Data and add the words.

create table

Now we will create a relation between these two data sources. We need the relations because the word cloud visual does not work with separated data sources. For this reason, we have to create relation between these two data sources. We will click Relationships.

relationships

We will create a relationship between two data sources. In the relationship tab we will click Manage Relationships.

new text

We will click the New button in the Manage relationships pane.

manage relationships

We will generate the relationship between two data sources.

new text

In this step we will exclude these characters in the Word Cloud. We will drag and drop the Column1 field to the excludes field.

column

After this step, the Word Cloud component automatically remove these words. At the same time, we can change the word cloud visual formats.  You can find several properties under the format tab. Such as you can change the title or word cloud visual and properties of the title or you can set width and height of the word cloud. In this demonstration we will only change the Rotate Text Property.

visualizations

Now we will close the rotate text property. We will click on the word cloud and close the Rotate Text property.

new text
with rotate

In this tip we briefly discussed how to use the Word Cloud component in Power BI. The Power BI Word Cloud Visual offers a great solution for analyzing text, an article or a similar type of word strings.

Next Steps
  • Create a sample Notepad file and use this file as a data source or connect to another data source in Power BI. Then connect this data source to the word cloud visualization.
  • Change the word cloud component format properties.
  • Read below article to explore Power BI capabilities.


Last Updated: 2018-05-01


get scripts

next tip button



About the author
MSSQLTips author Esat Erkec Esat Erkec is a Microsoft certified SQL Server Database Administrator that has been working with SQL Server since 2004.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, August 19, 2019 - 8:08:24 AM - Esat Erkec Back To Top

You can review the below comment, I would guess that you can overcome the issue through that little changing. Or you can consider this alternative solution;

- Append Table1 data to New_Text in the PowerQuery and then please apply the following steps
- Edit relation and change it to Many to one (*.1)
- Click Refresh


Tuesday, July 02, 2019 - 7:10:37 PM - Everson Back To Top

I think this solution is missing logic. It's impossible to join tables by two text columns. How does the tool resolve this: main column = " the cat is black" and the word stop column = "the"? Will the tool use 'like' function for joining? Anyway, I try and it didn't work. 

Best regards


Thursday, September 20, 2018 - 12:32:25 AM - ESAT ERKEC Back To Top

You are definitely right. 

One to one (1:1) relationship could be cause problem. We can handle this issue in two step.

 - Add the exclude  words to word cloud source text  row by row

 - Change the cardinality Many to one (*.1)

 - Click refersh

These steps will resolve your problem.


Tuesday, September 18, 2018 - 2:11:27 PM - Nancy Peterson Back To Top

I'm having an issue with the step on creating the relationship between the text field and the word field for the exclusion words. When I attempt to create the One to One relationship, I receive an error message that "the cardinality you selected isn't valid for this relationship." The "Many to one" option does not result in any of the words being removed from the word cloud. What am I missing?



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools