Hi! And thank you for reading this blog post. My name is Miguel Escobar and I’m one of the authors of the book ‘M is for Data Monkey’ and co-founder of the PowerQuery.Training project. I also hold the MVP distinction and since 2013 I’ve been acting as an international BI consultant working with the Power BI family of tools starting with Power Pivot and more recently with Custom Data Connectors using Power Query and its ‘M’ language.
Recently Microsoft made publicly available a preview feature called ‘Custom Connectors’ and I spent 2 weeks, after that announcement, reading the tutorials from Microsoft and creating my own Custom Connector for my Youtube Channel until it was fully documented and in its final stage.
I won’t bore you with a long technical blog post about how I created a Custom Connector (you can read everything about it on my github repo), but I can testify that is actually pretty easy to create a custom connector once you understand the basics of the extensibility mode and my intention with this blog post is to encourage you to try out my Custom Connector and perhaps create yours as well.
What are Custom Connectors?
Whenever you start Power BI Desktop and go through the ‘Get Data’ Experience, you see a list of Data sources that you can connect to. Each of the elements that you see in the Get Data Experience is essentially a Data Connector that comes out of the box in Power BI Desktop.
A custom connector is basically a Connector that you can add to this ‘Get Data’ window so you can connect to a data source of your choice, with your own logic and with multiple options for type of credentials like the ones that you’re familiar with like Windows, basic and database authentication to even setting up your own OAuth 2.0 workflow.
You create these custom connectors with the same language that Power Query (Get and Transform for Excel 2016) uses, which is a language called ‘M’.
Here's a list of the key capabilities that a custom connector can bring:
Creating a business analyst friendly view for a REST API
Providing branding for a source supported by an existing connector (such as an OData service, or ODBC driver)
Implementing an OAuth v2 authentication flow for a SaaS offering
Exposing a limited/filtered view over your data source to improve usability
Supporting different authentication modes when creating a Power BI Content Pack
Enabling DirectQuery for a data source via an ODBC driver
Here’s a previous article on what Custom Connectors are and what capabilities it could bring to your Get Data experience.How a Custom Connector will show up in the 'Get Data' WindowHow can I create a Custom Connector?
Here’s a list of what you’ll need:
Good knowledge of the M language
Latest version of the Power BI Desktop (June 2017)
Visual Studio 2015 or 2017
The Power Query SDK extension
Fiddler for Web calls tracing
Depending on your data source, you might need to read a lot of documentation to understand the best way to query your data
You can get started with the tutorials posted by Matt Mason on his TripPin series here and also the whole documentation found at their Github repo here.
The basic installation would require you to have Visual Sutdio with the Power Query SDK and with that you’ll be able to create a new Data Connector Project from within Visual Studio. The project will automatically start with the needed information that you just simply need to fill out in order to create your first Custom Connector.
The .pq file is the one that has all the logic of your connector
What can I do with a Custom Connector? Show me some examples!
I’m a pretty visual person, so I’d like to show you with some images what a custom connector like the one that I created for the Youtube Data API and the Youtube Analytics API with OAuth 2.0 looks like:
Custom Table Navigation for the elements exposed in the Connector
Fully documented function with sample values for parameters
Youtube Channel Summary
Attempt at creating a benchmark for videos with the tag "Power BI"Report for the Performance of a video
If you have a Youtube Channel and would like to give this connector a try, then click the button below to find out more information on how to get this connector and its companion Report Template. If you need help with a custom connector you can also contact me through Github! Looking forward to hearing from you.