How to Create a Power BI Customer Connector that Fetches Data Using The Twitter API V2

Recently I had to create a Custom Connector that could extract data from a REST API and serve this up in Power BI for reporting and visualisation.  This was totally new to me, involved a several steps, and was initially tricky to put together and debug.

 

In this blog post I document the main steps that are involved when creating Custom Connectors that need to consume REST API with authentication.

 

Specifically, the following is covered:

• Creating a custom connector using the M scripting language
• Adding authentication headers
• Fetching tweets and integrating the Twitter API v2
• Parsing JSON data Contents and Json.Document
• Converting data to tables using FromList using Table.ExpandRecordColumn

 

Prerequisites

To implement this, you need a Developer account with the Twitter API v2.  The following is also needed:

• Power Query SDK
• VS 2019
• Power BI Desktop

 

Note: At the time of writing this blog, Data Connector Projects aren’t supported in Visual Studio 2022.

 

Data Connector Project

Installing the Power Query SDK gives you access to a new project type called the Data Connector Project.

 

Adding one of these projects to your Visual Studio Solution creates several assets. For reference, these include:

• Connector definition file (<connectorName>.pq)
• A query test file (<connectorName>.query.pq)
• A string resource file (resources.resx)
• PNG files of various sizes used to create icons

 

 

Anatomy of a Custom Connector Script

Custom connector script is written in a language called M. I’d never heard of this before so was new to me but was able to decipher the commands I needed using online documentation.

A custom connector definition consists of a few components:

• section statement
• data source definition
• authentication record
• publish record
• icon definitions

 

You can see an example of a simple hello world one here:

section HelloWorld;

[DataSource.Kind="HelloWorld", Publish="HelloWorld.Publish"]

shared HelloWorld.Contents = (optional message as text) =>

let

message = if (message <> null) then message else "Hello world"

in

message;




HelloWorld = [

Authentication = [

Implicit = []

],

Label = Extension.LoadString("DataSourceLabel")

];




HelloWorld.Publish = [

Beta = true,

ButtonText = { Extension.LoadString("FormulaTitle"), Extension.LoadString("FormulaHelp") },

SourceImage = HelloWorld.Icons,

SourceTypeImage = HelloWorld.Icons

];

HelloWorld.Icons = [

Icon16 = { Extension.Contents("HelloWorld16.png"), Extension.Contents("HelloWorld20.png"), Extension.Contents("HelloWorld24.png"), Extension.Contents("HelloWorld32.png") },

Icon32 = { Extension.Contents("HelloWorld32.png"), Extension.Contents("HelloWorld40.png"), Extension.Contents("HelloWorld48.png"), Extension.Contents("HelloWorld64.png") }

];

Source: Microsoft

 

The core logic of the above M script is this part here:

shared HelloWorld.Contents = (optional message as text) =>

let

message = if (message <> null) then message else "Hello world"

in

message;

 

Think of it as a function that accepts an input parameter then outputs a response to the console.  It’s in here where you can inject additional logic.

Creating a Connector That Fetches Data Using Twitter API v2

Creating a connector that fetches data from the Twitter API V2 is more involved than the hello world example from above.

Main steps include:

• creating an authenticated request with a bearer token
• parsing JSON data that contains tweets
• finding the relevant data in an array from the Twitter API payload
• converting the data to a consumable format for reporting

 

The query being sent the to Twitter API should be configurable too so that needs to be considered.

M Script for the Twitter API V2 Connector

The entire script for the Twitter API Custom Connector is detailed below and lives in a file called TwitterAPIConnector.pq.

 

Below we see the section and function definition. We can see the function accepts a query and returns a Json.Document.  This type is used as the Twitter API will return a JSON payload:

 

section TwitterAPIConnector;


[DataSource.Kind="TwitterAPIConnector", Publish="TwitterAPIConnector.Publish"]

shared TwitterAPIConnector.GetTweets =

Value.ReplaceType(TwitterAPIConnectorImpl, type function (query as text) as any);


TwitterAPIConnectorType = type function (

query as (type text meta [])

) as Json.Document;

 

When the custom connector is used in PowerBI, a prompt will be presented asking for a query to send to the Twitter API.

 

Below we can see the implementation of the function definition:

TwitterAPIConnectorImpl = (query as text) =>

let

headers = [

#"Authorization"="Bearer [BEARER_TOKEN]",

Accept = "application/json;odata.metadata=minimal"],




Source = Web.Contents(query, [ Headers = headers ]),

json = Json.Document(Source),

records = json[data],

#"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"author_id","text","created_at"})

in

#"Expanded Column1";




// Data Source Kind description

TwitterAPIConnector = [

Authentication = [

Anonymous = []

],

Label = "Twitter API Custom Connector v0.1"

];




// Data Source UI publishing description

TwitterAPIConnector.Publish = [

Beta = true,

Category = "Other",

ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },

LearnMoreUrl = "https://www.jamiemaguire.net/",

SourceImage = TwitterAPIConnector.Icons,

SourceTypeImage = TwitterAPIConnector.Icons

];


TwitterAPIConnector.Icons = [

Icon16 = { Extension.Contents("TwitterAPIConnector16.png"), Extension.Contents("TwitterAPIConnector20.png"), Extension.Contents("TwitterAPIConnector24.png"), Extension.Contents("TwitterAPIConnector32.png") },

Icon32 = { Extension.Contents("TwitterAPIConnector32.png"), Extension.Contents("TwitterAPIConnector40.png"), Extension.Contents("TwitterAPIConnector48.png"), Extension.Contents("TwitterAPIConnector64.png") }

];

 

From the above M script, you can see the authentication header with a bearer token being defined.

 

A variable Source is used to stored result of the web request made with the command Web.Contents.

 

For reference, the payload from the Twitter API will be in the following structure:

{

"data": [

{

"author_id": "2244994945",

"created_at": "2020-06-11T16:05:06.000Z",

"id": "1271111223220809728",

"text": "Tune in tonight and watch as @jessicagarson takes us through running your favorite Python package in R. \n\nLearn how to use two powerful programming languages for data science together, and see a live example that uses the recent search endpoint from Twitter’s Developer Labs. https://t.co/v178oUZNuj"

},

{

"author_id": "2244994945",

"created_at": "2020-06-10T19:25:24.000Z",

"id": "1270799243071062016",

"text": "As we work towards building the new Twitter API, we’ve extended the deprecation timeline for several Labs v1 endpoints. Learn more  https://t.co/rRWaJYJgKk"

}

],

"includes": {

"users": [

{

"description": "The voice of Twitter's #DevRel team, and your official source for updates, news, & events about Twitter's API. \n\n#BlackLivesMatter",

"id": "2244994945",

"name": "Twitter Dev",

"username": "TwitterDev"

}

]

},

"meta": {

"newest_id": "1271111223220809728",

"oldest_id": "1270417572001976322",

"result_count": 2

}

}

 

Other key steps in the M script include:

 

• data is then parsed to a Document and stored in the variable json.  This step was crucial in making it possible to fetch tweets from the Twitter API payload.
• Tweets from the Twitter API returned in an array called data using the command json[data] lets you reference the array of tweets.
• FromList accepts the array/list of tweets and converts them to a table structure
• ExpandRecordColumn extracts the data from each tweet and rows using a table structure

 

Meta data is stored in the TwitterAPIConnector.Publish and TwitterAPIConnector.Icons sections and details how the connector is surfaced in Power BI.

Testing the Twitter API V2 Power BI Custom Connector

A second file is needed to test the connector.  The is TwitterAPIConnector.query.pq and acts as a simple test harness. It lets you invoke the M script above.

You can see this here:

// Use this file to write queries to test your data connector

let

result =

TwitterAPIConnector.GetTweets("https://api.twitter.com/2/tweets/search/recent?query=from:jamie_maguire1&tweet.fields=created_at&expansions=author_id&user.fields=username")

in

result

 

In the above M script, you can see the query uses the Recent Search API and sets the query param to jamie_maguire1 (my Twitter handle).  Doing this will fetch a list of my recent tweets.

You can test this by clicking run in in Visual Studio.  The first time you do this you’re prompted for credentials.

Set Credential Type to Anonymous and Privacy to None. Click Set Credential and you’re given a confirmation it’s been successful.

 

You need to click Run one more time in Visual Studio.  After the M script is invoked, the query output window displays some recent tweets:

 

With the custom connector now validated it can be used in Power BI Desktop.

Using the Custom Connector in Power BI

When you create and build in the custom connector in Visual Studio, a *.mez file is created in the debug folder (TwitterAPIConnector.mez).

 

You need to copy this file into the following location: C:\Users\{username}\Documents\Power BI Desktop\Custom Connectors

 

If this path doesn’t exist, then you need to manually create it.  After you have done this you can launch Power BI.  Power BI will identify the new connector and display the following prompt:

 

You can click OK to bypass this.  The following screen is displayed:

 

 

Click Get Data.  You may see the following prompt:

 

 

If this is displayed, then you need to amend your Power BI security settings.

Browse to the following settings in PowerBI: File->Options and Settings->Options and select Security then under Data Extensions select the following radio button:

 

 

Click OK and restart PowerBI.

When in Power BI loads select Get Data and search for Twitter.  The list will filter, and the new custom connector will be displayed:

 

 

Select the custom connector and click Connect.  When it loads you will be prompted to select input a query.

Use the following but substitute the {username} parameter for your Twitter username:

https://api.twitter.com/2/tweets/search/recent?query=from:{username}&tweet.fields=created_at&expansions=author_id&user.fields=username"

 

Click OK and then click Connect :

 

 

The custom connector will work its magic and fetch tweets using the Twitter API:

 

 

You can then click Load  and create reports or visualisations in PowerBI use recent tweets from the Twitter API V2.

 

Demo

You can see this in action in a short demo recorded and upload to YouTube. In demo you’ll see:

• M script in Visual Studio
• Setting authentication for the query
• Testing the M script in Visual Studio
• Using the Twitter API V2 Custom Connector in Power BI

You can view the YouTube demo here.

Summary

Custom connectors let you create off the shelf components for other people to use. Debugging them in Visual Studio was painful and involved a lot of trial and error.  That might be down to me being new to this side of development.

I found the following references and documentation useful when getting started with custom connectors:

• Installing the Power Query SDK – Power Query | Microsoft Docs
• Start developing custom connectors for Power Query – Power Query | Microsoft Docs
• Creating your first connector – Hello World – Power Query | Microsoft Docs
• TripPin 2 – REST – Power Query | Microsoft Docs