How to Connect Power BI to an External API Using OAuth 2

Jan 21 2020

Power BI is an excellent tool for data visualization. The tool enables the user to connect it to external API’s. This power is especially useful as these days a lot of apps and websites have API’s to use for BI purposes.

For the sake of this tutorial, let’s work with the twitter API. Similar can be done for other API’s you just have to change the first step which is related to creating tokens necessary to access an API

Important Note:- The method I am going to discuss in this article only works with API’s that do not have a redirect URL or it is optional. For example, the API for twitter fits this description. If an API has a redirect URL, for example, that of Spotify has a redirect URL so in that case a custom connector needs to be built. That is covered in a separate article on the blog.

Create A Twitter Application

To use the Web API, first create a twitter developer account

When you have a user account, go to the Dashboard page at twitter, and create an app.

The app is required so that we can use the keys and tokens to access the twitter search API.

Connecting Power BI to the API

After you are inside Power BI, go to the query editor. The best way to do this would be to create a blank query.

Once the blank query is created, you can use the advanced editor to actually write the code to access the API.

Code For Accessing the API

For accessing the API, you need a script with two stages, in the first step you actually get the access tokens from the OAuth script and in the second step you access the information.

The first step for this is to first create parameters for the query.

Create 4 new parameters as follows:
API Key: <<your API Key>>
API Secret: <<your API Secret>>
Token URL: https://api.twitter.com/oauth2/token
Search URL: https://api.twitter.com/1.1/search/tweets.json?q=statistics&count=100

The best script I found for this purpose was from Chris Koester . Paste this script in the advanced query editor

/*
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 & 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 & Consumer Secret and converts to base64
 authKey = "Basic " & Binary.ToText(Text.ToBinary(#"API Key" & ":" & #"API Secret"),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 " & 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=statistics&count=100",
     [
         Headers = [#"Authorization"=AccessTokenHeader]
     ]
 ),
FormatAsJsonQuery = Json.Document(GetJsonQuery)
in
    #"FormatAsJsonQuery"

I am searching for Statistics but you can search for any term that you care for.

Creating Visualizations

Once the data is loaded we will apply transformations to it in the query editor, we will expand on the entities column, to get the text of the hashtag column.

Here are the main transformation that we undertake:-