Get Data from Twitter API with Power Query

For my inaugural Power Query post, I’ll show how you can get data from the Twitter API, specifically the Twitter GET search/tweets method.

The web API is one of my favorite data sources in Power Query, because it opens a new world of data to Excel users. There are literally thousands, and perhaps tens of thousands of APIs out there. Some examples include Data.gov, United States Census, Weather Underground, and Twitter.

The Twitter API does require authentication, so you will need a Twitter account. Once you have an account, here are the steps:

Create a Twitter application here - https://apps.twitter.com/
1. On the Permissions tab, I set mine to Read only. The other permission levels allow the application to author Tweets on your behalf or even access direct messages, so be careful.
2. If you’re only using the application for personal data retrieval, the other settings are not that important.
On the Power Query Options menu in Excel, select Privacy and then select “Ignore the Privacy levels and potentially improve performance”. This is required because a single query is combing multiple data sources (One to get a token and one to get the data).
Copy the code below into the Power Query Advanced Editor.
Enter the Consumer Key and Consumer Secret from your Twitter application on line 13. It should look like this: “xxxxxxxxxxxxxxxxxxxxxxxxx:xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”. IMPORTANT - the application keys should be treated as passwords. In some scenarios you can use the ApiKeyName option of the Web.Contents formula to store the key securely in the credential store. Unfortunately I was not able to get it to working in this case. I believe it is incompatible with the Content option, which makes the request a POST. See Chris Webb’s post about using the ApiKeyName option.
Enter your query on line 28. My sample code queries for Allegient, which is my employer’s name (Shameless plug).
Close and Load the query!

/*
This M script gets an bearer token and performs a tweet search from the Twitter REST API
https://dev.twitter.com/oauth/application-only

Requires establishing a Twitter application in order to obtain a Consumer Key &amp; Consumer Secret
https://apps.twitter.com/

IMPORTANT - The Consumer Key and Consumer secret should be treated as passwords and not distributed
*/

let
 // Concatenates the Consumer Key &amp; Consumer Secret and converts to base64
 authKey = "Basic " &amp; Binary.ToText(Text.ToBinary("&lt;ConsumerKey&gt;:&lt;ConsumerSecret&gt;"),0),
 url = "https://api.twitter.com/oauth2/token",
 // Uses the Twitter POST oauth2/token method to obtain a bearer token
 GetJson = Web.Contents(url,
     [
         Headers = [#"Authorization"=authKey,
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
         Content = Text.ToBinary("grant_type=client_credentials") 
     ]
 ),
 FormatAsJson = Json.Document(GetJson),
 // Gets token from the Json response
 AccessToken = FormatAsJson[access_token],
 AccessTokenHeader = "bearer " &amp; AccessToken,
 // Uses the Twitter GET search/tweets method using the bearer token from the previous POST oauth2/token method
 GetJsonQuery = Web.Contents("https://api.twitter.com/1.1/search/tweets.json?q=Power BI&amp;count=100",
     [
         Headers = [#"Authorization"=AccessTokenHeader]
     ]
 ),
 FormatAsJsonQuery = Json.Document(GetJsonQuery),
 NavigateToStatuses = FormatAsJsonQuery[statuses],
 TableFromList = Table.FromList(NavigateToStatuses, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 ExpandColumn = Table.ExpandRecordColumn(TableFromList, "Column1", {"metadata", "created_at", "id", "id_str", "text", "source", "truncated", "in_reply_to_status_id", "in_reply_to_status_id_str", "in_reply_to_user_id", "in_reply_to_user_id_str", "in_reply_to_screen_name", "user", "geo", "coordinates", "place", "contributors", "is_quote_status", "retweet_count", "favorite_count", "entities", "favorited", "retweeted", "lang", "possibly_sensitive", "quoted_status_id", "quoted_status_id_str", "quoted_status"}, {"Column1.metadata", "Column1.created_at", "Column1.id", "Column1.id_str", "Column1.text", "Column1.source", "Column1.truncated", "Column1.in_reply_to_status_id", "Column1.in_reply_to_status_id_str", "Column1.in_reply_to_user_id", "Column1.in_reply_to_user_id_str", "Column1.in_reply_to_screen_name", "Column1.user", "Column1.geo", "Column1.coordinates", "Column1.place", "Column1.contributors", "Column1.is_quote_status", "Column1.retweet_count", "Column1.favorite_count", "Column1.entities", "Column1.favorited", "Column1.retweeted", "Column1.lang", "Column1.possibly_sensitive", "Column1.quoted_status_id", "Column1.quoted_status_id_str", "Column1.quoted_status"})
in
 ExpandColumn

Notes:

Twitter limits you to only 100 rows at a time, so don’t be surprised by the limited results.
This pattern should work for other Twitter APIs. Just change the URL on line 28 and the column names as appropriate.
Kasper de Jonge has also written about using Power Query with the Twitter API. I believe I’ve improved upon it by automating the retrieval of the token.

Notes:#

Notes: