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: