How to Connect Power BI to an External API Using OAuth 2
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:-
- Expand the list to a table

2) Expand the entities column till you get the final text of the hashtag.

Here is the final table, you can select fewer columns also if you want. Finalize the query by clicking on close and apply button on the top.

Here is the final visualization you get about the count of the hashtags.

No comments yet.
Add your comment