Import Twitter data to Excel & Power BI using Microsoft Flow or Zapier

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.

Twitter Analytics Report on Power BI
Twitter Analytics Report on Power BI

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.

1

Select Excel Workbook.

2

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.

3

In Create Table window, select My table has headers, and click OK.

4

Back in OneDrive folder, right click on the file Book.xlsx, and select Rename.

5

In the Rename dialog, change the name to Twitter.xlsx and click Save.6

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.

7

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.

screenshot_11 screenshot_12

To authorize Microsoft Flow to search for tweets, provide your user and password and click Authorize app.

screenshot_13

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.

screenshot_38

Click Add an action.

screenshot_39

Start typing excel, and select Excel – Insert row.

screenshot_40

Select OneDrive for Business.

screenshot_2

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.

screenshot_3

Continue browsing in sub folders till you find the workbook, and click on Twitter.xlsx.

screenshot_4

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).

screenshot_5

Now, Microsoft Flow will recognize Table1. Select that table.

screenshot_6

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.

screenshot_7

Click on the DateTime box, and then click on the “…” icon.

screenshot_8

Click Created at.

screenshot_9

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.

screenshot_1

Now, at the top of the page, let’s rename the flow to Microsoft Flow Mentions, and click Create Flow.  screenshot_12

We can now click Done.

screenshot_13

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:

screenshot_15

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!

screenshot_26

Start typing “twitter” and select Twitter as the Trigger App.

screenshot_25

Select User Tweet and click Save + Continue.

screenshot_44

Click Connect a New Account.

screenshot_27

Follow the steps till you see the screen below and click Authorize app.

screenshot_28

Back in Select Twitter Account dialog, click Save + Continue.

screenshot_29

Set the Username (in my case: @realDonaldTrump), and click Continue.

screenshot_45

Click Fetch & Continue.

screenshot_46

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.

screenshot_49

Click Connect a New Account.

screenshot_30

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:

screenshot_31

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:

screenshot_32

Select Sheet1 as Worksheet.

screenshot_33

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.

screenshot_34

Continue the assignment of Twitter fields to the Excel columns as shown in this screen:

screenshot_35

Two side notes:

  1. The column Source couldn’t be populated by Microsoft Flow. The Twitter field Source wasn’t available.
  2. 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.

screenshot_52

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.

screenshot_36

Make sure you turn on the second Zap, and you are done.

screenshot_53

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.

screenshot_37

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.

Subscribe ToDataChant

Join our mailing list to receive exclusive content that was used in the preparation of this article.

Thank you for subscribing to DataChant. An email will be sent to you shortly with more details.

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).

screenshot_16

Click Get in Files.

screenshot_17

Click OneDrive – Business.

screenshot_18

Select Twitter.xlsx file and click Connect.

screenshot_20

Click Import.

screenshot_21

Click View dataset, and start building your report.

screenshot_22

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.

screenshot_23

That’s it for today. Hope you found this tutorial useful. Contact me at [email protected] if you wish to purchase fully customized Twitter Analytics reports without relying on an external workflow automation service.

Leave a Reply