One of the visualizations that I really love is tag cloud, or Word Cloud as it is called in Power BI. It usually grabs the viewer’s attention and provokes emotional reactions that can help you to touch your audience. I have used it fervently in key presentations in the past (Here is a great website to create tagclouds for ad hoc presentations), and was eager to find a good excuse to use it, when Power BI announced the Word Cloud custom visual.
So today we will build a Word Cloud 🙂
To make it interesting, I thought it would be cool to continue our Facebook series and build a Power BI report with a Word Count of the Facebook posts of all the US Election Candidates. The tutorial will have two parts. Today we will focus on the first one that builds a quick and dirty word cloud report.
Here is the Word Cloud that we will prepare:
In this tutorial you will learn how to extract your posts from your brand’s Facebook Page and load the words into Power BI (Or Excel, if you are familiar with the Power Query / Get & Transform user interfaces). You will also learn some cool tricks that will help you to extract multiple offsets from Facebook Graph API, and to load multiple pages (using the query function technique).
The basic Facebook import
We will start by opening Power BI Desktop, clicking the Get Data button, selecting Other -> Facebook and clicking Connect.
The Facebook dialog will appear. In the first text box enter DonaldTrump and in the second text box select Posts, then click OK.
If this is the first time importing data from Facebook with Power BI Desktop, you will get through Facebook permission approval steps.
When you are done, you will encounter the “Unable to Connect” error dialog below (I consider it as a bug, but as a Software Engineer I know why my friends at Microsoft might treat it as a typical by-design experience, or a Facebook limitation). Let’s not let this error intimidate us, shall we?
The error appears because Facebook blocks Power BI API call as it “thinks” that Power BI requests too many posts on a single API call. Let’s use simple steps to pass this obstacle. We will hook into the Power Query facebook formula and explicitly send a message to Facebook Graph API stating that we are limiting our request to 100 posts (Later we see how we can iterate over more posts).
Click Edit in the Unable to Connect dialog.
Change the Connection value from Posts to —None— and click OK.
You will now see all the root-level records of Donald Trump’s Facebook page. Make sure that your Formula Bar is visible (You can activate it by checking the Formula Bar check box in View tab).
Modify the string that is passed in Facebook.Graph function, and add the following suffix:
In addition, change the Facebook version from v2.2 to v2.5 as seen in the screenshot below. Press enter and you will see a preview with the posts from Donald Trump’s Facebook page.
For the simplicity of this tutorial let’s focus only on two columns: message and created_time. Select Choose Columns in Home tab, then check these columns in the Choose Columns window, and click OK.
Click Close & Apply.
The quick and dirty Word Count report
By following the steps above, we have loaded 100 posts from Donald Trump’s Facebook page and loaded them into the Power Bi in the format of two columns. The message columns contains the entire posts, each post in a separate cell. The second column is for the date and time of the post (but we will not need it in this tutorial).
Let’s try to create quick and dirty word count based on the current data.
Download the Word Count visual from Power BI Gallery here.
Click on the “…” icon in the Visualizations pane. Read the politically correct warning that follows, and click Import.
Locate the WordCloud.pbiviz file and import it. You will get the following dialog. Instructions are not necessary here 🙂
Notice that you now have a new icon in the Visualizations pane. Click on this icon.
Now, looking at the Fields pane, select the message field under Query1, and wait till the Word Cloud visual will be generated in the main canvas.
At this stage you may get infuriated by the fact that the words “the”, “to” and “and” are bigger than the word “Trump”. Fortunately there is a way to ignore common words with minimal efforts.
Select the Word Cloud visual in the main canvas, and click on the brush icon in Visualizations pane.
Set On in the Stop Words section and expand it. Then select On in the Default words.
That’s it. Miraculously, the cloud turns into a meaningful mashup of words.
In my next blog post, we will continue this tutorial, and expand the Word Cloud to analyze the posts of the presidential candidates.
Have a great weekend,