Presidential Word Cloud in Power BI – Part 2

As promised, today we will continue working on our Presidential Word Cloud that we have started here, and will focus on two important and useful techniques which are not only relevant for Facebook analysis in Power BI:

1. We will learn how to extract words from textual columns without splitting the column to many columns.
2. By splitting messages to single words, our Word Cloud visual in Power BI will work better, we will be able to resize it, and drill down from a single word to its original message.

Oh, and the relation to Ghostbusters will be revealed at the end.

To recap our current challenge – In our last tutorial we have created a Word Cloud which is not resizable.

To fix this limitation, we will need to create a new column in our query for the individual words, and feed this column to the Word Cloud visual.

Let’s start.

Right click on column message and click Duplicate Column.

Rename the new column to word, right click on the header, and select Transform –> lowercase. This step will lowercase all the messages and treat words like “They” and “they” as the same word in the visualization.

Now let’s replace all line feeds with spaces. This step will help us to address text such as this one:

Hello world!

How are you?

And we will convert it to:

Hello world! How are you?

Right click on the column word and select Replace Values…

In the Replace Values dialog, check the box Replace using special characters, and select Line Feed.

Notice that the text #(lf) was added in the first edit box. #(lf) is the special characters that represent line feed in Power Query. You can use it in the formula bar whenever you want to perform string manipulations that includes line feed.

Enter space character in the Replace With, and click OK.

Next step is a bit more advanced. It will allow you to split the column word into separate words, without getting into the risky Column operation of Splitting a single column into multiple ones (I explained the risk of splitting columns here).

We start by creating a list of delimiters.

Click Enter Data.

In Create Table dialog, enter delimiter as a header, and then on each cell enter different delimiters such as: space, comma, semicolon, colon etc.

Name the table as Delimiters, and click OK.

Right click on the column delimiter and click Drill Down.

You will see that your table was transformed into a list. We will use this list in the next steps.

Note: If you are an advanced Power Query user, you may prefer using a query such as this one, instead of manually entering the delimiters into a table. Then it may be easier for you to manipulate the list when you need to add new delimiters.

={” “, “,”, “.”, “?”, “!”}

But for basic users, it is easier to manage your delimiters through the Create Table UI as we have done above. You can go back to that list, and modify it by clicking on the cog wheel icon (highlighted below) in the Source step.

Now, let’s go back to Query1, right click on the column header word, and select Transform –> lowercase again. We perform this step as a foundation for the split operation with the list of delimiters we have just created. This step has no other significance (Who will perform double lowercase operations on the same data, right?)

Now, mark the highlighted text in the formula bar and replace Text.Lower with:

Splitter.SplitTextByAnyDelimiter(Delimiters)

Here is a screenshot of the modified formula:

And here is where the magic happens… Power Query transforms the column word from a text into a list. There is no intermediate step that will split your column into multiple ones.

Now, let’s click on the expand button which is highlighted below: 

After the expand step, you can see that each word in the message gets a dedicated row with its message.

Let’s filer out numbers, words that starts with numbers and any string with less then 3 characters.

Click on the filter icon in the column header word. Uncheck the values null and (blank), and click OK.

In the formula bar add the following formula to the filtering condition to keep words with more than 2 characters:

and Text.Length([word]) > 2

Now, let’s add a new filter by clicking again in the filter icon, selecting Text Filters and clicking Does Not Equal… (or any other option).

In the Row Filters dialog enter aaa as values, and click OK. This step is an intermediate step. You should not try to figure out its intrinsic logic.

Now let’s delete the section [word] = “aaa” in the formula and change it to

Value.FromText(Text.At([word], 0)) is number

This step select all the rows whose text in the column word starts with a number.

In the next step we will filter out these rows by negating the logic in the formula.

Negate the condition in the formula by adding not (…) to the condition above.

not (Value.FromText(Text.At([word], 0)) is number)

Here is the finished formula:

Now you are ready to go. Click Home –> Close & Apply, and rebuild the Word Cloud by associating the field word to Category. 

Don’t forget to activate the Stop Words and Defaults in the Visualizations pane as shown below (This step will allow the Word Cloud visual to ignore common words like “the”, “to” and “for”):

Now, at last, you can resize your Word Cloud visual 🙂

 

Here are few bonus refinements:

If you prefer to Capitalize each word in your Word Cloud, you can go back to Query1 and apply the following step:

You may notice that the Word Cloud visual ignore special characters like $. As a result, you will see in the Word Cloud some numbers, even though we filtered out words that started with numbers. For example, the word $100 will be represented as 100 in the report. You can add the $ character to the list of Delimiters to avoid these words. I described above how to modify the list of delimiters.

Finally, you may want to drill down from a single word to the original message. To do it, you can add a new visual like the Multi-Row Card, and assign the field message to the Fields box.

Now whenever you click on a single word in the Word Cloud, you will see the original message.

At this stage you might be wondering why the word Ghostbusters was highlighted in comparison to other words that were used on Donald Trump’s page.

We forgot to define how the Word Cloud should count the word repetition. To do it, simply frag and drop the field word to the Values box, and you are ready to go.

Have a  happy Word-busting 🙂