Today we will learn two simple methods to import ongoing Twitter data to Excel & Power BI. The methods will take advantage of Microsoft Flow and Zapier, two workflow automation services that will allow us to easily import new tweets into an Excel workbook on OneDrive for Business. The Excel file will be updated whenever a new tweet will be detected by the services, and will be used as the external data source for the Power BI report. Following the steps of this tutorial, you will we be able to use Power BI Desktop to build a Twitter Analytics report, and publish it to Power BI cloud service with a fully automated scheduled data refresh.
Before we start, check out this Power BI report that analyzes the data we will import in this tutorial. Subscribe to DataChant to download this report.
Introduction
Power BI and Power Query (Get & Transform) in Excel have many useful connectors. Unfortunately, you will not find a Twitter connector in that list. As a result, you need to rely on external services or advanced queries to tap into the desired tweets.
For example, you can use Power BI Brand & Campaign Management for Twitter here, which requires an Azure subscription, and as a content pack lacks the capability to edit the reports in Power BI Desktop, and mashup the data with other external sources. You can also create an all-in-Power-BI-desktop solution using the Web connector and advanced queries. For example, you can follow this great blog post by Chris Koester and extend it to load multiple pages by implementing a cursor-based pagination (as I demonstrated here). While I am currently using the latter approach in my social analytics dashboards, I wanted to share with you a simpler solution, which is amazingly simple, and doesn’t require Azure subscription or Twitter Developer App.
The middle workbook
Before we start fetching tweets, we should first create an Excel file on OneDrive for Business with a table that will be used to store the tweets. The selection of OneDrive for Business as the service to host the data is important. It will gain us an automatic scheduled data refresh on Power BI, when we are done.
Go to your OneDrive for Business, and click New.
Select Excel Workbook.
In Excel Online, fill the first row with the following values (each value in a separate cell): Tweeted By, DateTime, Text, Retweets, Favorites, and Source.
Select one of the cells, go to the INSERT tab and click Table.
In Create Table window, select My table has headers, and click OK.
Back in OneDrive folder, right click on the file Book.xlsx, and select Rename.
In the Rename dialog, change the name to Twitter.xlsx and click Save.
Microsoft Flow vs. Zapir – Which workflow automation service to pick?
In this tutorial, you can use either Microsoft Flow or Zapier to automate the data import from Twitter to Excel. We will start with Microsoft Flow, which is still in preview, and lacks some crucial scenarios, and then move to Zapier.
While Microsoft Flow allows you to search for specific new mentions by given keywords, Zapier supports these additional scenarios:
- When a search term in a given geo location is detected
- When you tweet something new
- When a specific user likes a tweet
- When a specific user tweet
- When there is a new tweet in a specific list
- When you have a new follower
- When a specific user has a new follower
Zapier provides a better user experience when you select the Twitter fields, as it fetches the last tweet, and allows you to view actual values before you select the fields that you need. This experience helps you to select the right fields without the need to guess, or learn the Twitter API.
While I am sure that Microsoft will move fast to bridge the gap above, and will have a tighter integration with other Microsoft services, for the time being (October, 2016), I find Zapier much more useful for my main scenario, fetching the US Election Candidate Tweets.
Microsoft Flow – Option 1
Create an account on Microsoft Flow, click My flows and then click Create from blank.
Start typing twitter in the search box, and select Twitter – When a new tweet is posted in the second box. If this is the first time you use Microsoft Flow and the Twitter connection, click Sign in.
To authorize Microsoft Flow to search for tweets, provide your user and password and click Authorize app.
Type a keyword in the Search text box. For my demo purposes, I typed @MicrosoftFlow, which allows me to find new tweets and mentions of Microsoft Flow’s official Twitter account. When you are done, click New step.
Click Add an action.
Start typing excel, and select Excel – Insert row.
Select OneDrive for Business.
Now, we will select our Twitter.xlsx file from the previous section.
Click on the browser icon on the right side of the File name box.
Continue browsing in sub folders till you find the workbook, and click on Twitter.xlsx.
If this is the first time you use Excel in Microsoft Flow, you may encounter the following error: Could not retrieve values. Failed to fetch: Table name. This is a bug, and there is a simple workaround. Delete the step (by clicking the “…” icon, deleting the step, and adding again Excel – Insert row as a new step).
Now, Microsoft Flow will recognize Table1. Select that table.
Microsoft Flow will populate the columns of Table1. It’s time to assign the actual Twitter fields from the previous Twitter step to the table columns.
Click on the Tweeted By box, and select Tweeted by from the Tweeter output fields.
Click on the DateTime box, and then click on the “…” icon.
Click Created at.
Continue assigning the Twitter fields to the Excel columns, as shown in this screenshot. If you don’t find the right Twitter fields, click the “…” icon to expand the list, and find the correct field.
Now, at the top of the page, let’s rename the flow to Microsoft Flow Mentions, and click Create Flow. 
We can now click Done.
To test the flow, tweet something nice with a mention of @MicrosoftFlow. Wait couple of minutes, and open Tweeter.xlsx file.
Here is what I had found in the workbook, when I tried it:
It worked!!!
From here you can build a Power BI report that will be refreshed automatically with new tweets on an hourly basis.
Zapier – Option 2
We will move now to an alternative option to import the tweets to our Twitter.xslx file on OneDrive for Business, using Zapier.com. This time we’ll build an automation that imports all of Clinton’s and Trump’s tweets, and use Power BI as the analytics tool.
Why did I choose Zapier? (Sorry Microsoft, I am sure you’ll improve the service soon, and I will be happy to try it again). Zapier has a bigger coverage of external services, and better support for my specific Twitter scenario, which needs to fetch all the tweets by specific users (In my case – Clinton’s and Trump’s tweets).
At the beginning I used Microsoft Flow, and tried to circumvent the need to search for keywords in tweets, by searching for an empty string or a space. Unfortunately, Microsoft Flow required a real keyword in the search, which wouldn’t allow me to get all ongoing tweets from a specific user.
Microsoft Flow also lack the ability to duplicate a flow, which is very useful in my specific scenario (To define a flow for one user on Twitter, and then duplicate it for a second user).
So let’s use Zapier to build the automation and feed Clinton’s and Trump’s tweets into the Twitter.xlsx file that we had created earlier.
Go to https://zapier.com, sign up and click MAKE A ZAP!
Start typing “twitter” and select Twitter as the Trigger App.
Select User Tweet and click Save + Continue.
Click Connect a New Account.
Follow the steps till you see the screen below and click Authorize app.
Back in Select Twitter Account dialog, click Save + Continue.
Set the Username (in my case: @realDonaldTrump), and click Continue.
Click Fetch & Continue.
Click Continue on the next screen, and we are done with the first step of the flow, or the “Zap” as Zapier calls it.
Now let’s connect to Excel. Start typing “Excel” in the search box and select Excel.
Select Add Row and click Save + Continue.
Click Connect a New Account.
After you login to Excel with the same account you used to create Twitter.xlsx on OneDrive for Business, click Save + Continue in the following screen:
The next steps are easier in Zapier than in Microsoft Flow. Select the folder on OneDrive for Business and the file Twitter.xlsx by expanding the following drop down menus:
Select Sheet1 as Worksheet.
Click on the highlighted icon on the right side of the Tweeted By text box, and select User Screen Name. You will notice that Zapier, in contrast with Microsoft Flow, populate the different fields with real values that make the selection step easier.
Continue the assignment of Twitter fields to the Excel columns as shown in this screen:
Two side notes:
- The column Source couldn’t be populated by Microsoft Flow. The Twitter field Source wasn’t available.
- Did you see we have a new column in Twitter.xlsx: __PowerAppsId__. This column was added by Microsoft Flow. You should ignore it (Dear Microsoft Flow team, I hope it’s a temporary solution. When you go GA, please don’t clutter Excel with a column that is used by your inside-logic. I am sure you can find other ways to implement it).
When you are done, click Continue. Rename the Zap (In my case: Trump’s Tweets), and turn it on.I
If you wish to monitor an additional user (Clinton, in my case) you can copy the Zap by clicking Copy in the right side menu icon.
Let’s rename the new ZAP (In my case: Clinton’s Tweets) and edit it.
When you are in edit mode, go to the first step, select Edit Options and set the second username you want to monitor (In my case @HillaryClinton). When you are done, click Continue.
Make sure you turn on the second Zap, and you are done.
That’s it. Our Twitter.xlsx file is now used to store ongoing tweets by Trump and Clinton, and if you followed both Microsoft Flow and Zapier, you will also have all the mentions of @MicrosoftFlow in the same table.
Publish to Power BI
You now have all the data in Excel and can create stunning reports with Power BI. Here is an example:
The report above was created with Power BI Desktop. You can subscribe to DataChant and download the report.
[rad_rapidology_inline optin_id=optin_5]
In this section we will show new users how to consume the Twitter.xlsx file directly from PowerBI web service.
Go to PowerBI.com and click the Get Data icon (highlighted below).
Click Get in Files.
Click OneDrive – Business.
Select Twitter.xlsx file and click Connect.
Click Import.
Click View dataset, and start building your report.
When you are done, click Save. From now on, Power BI will automatically refresh the report with new tweets from the Twitter.xlsx excel file.
That’s it for today. Hope you found this tutorial useful. Contact me at gilra@datachant.com if you wish to purchase fully customized Twitter Analytics reports without relying on an external workflow automation service.
