Getting set up
When I heard about custom data connectors for Power Query, I had assumed there would be a lot of work involved. While there is definitely quite a bit of work in implementing advanced features like query folding, creating your very first connector is simple.
So, first you need Visual studio installed and the Power Query SDK installed as well. Once you do that, you will see Power Query as an option when creating a new project. Visual studio will also have support for .pq or Power Query files.
Once you create a new data connector project, you are presented with two main Power Query files. The first one, is simply a test query you can run on demand to test your connector.
The other file is your data connector. It has a bit of boilerplate to specify the types of credentials it accepts and publishing details such as beta status. Otherwise there is just a little bit of code defining the actual functionality. In this case we are defining the Contents function, which acts as a hello world:
If we run it as is, our test query will be run and we’ll see the results in a testing program.
Adding a function
So now, what if we want to add some more functionality? Say maybe a function to square numbers. First, we’ll add a SquareNumbers.Squared function to the main file:
shared SquareNumbers.Squared = (x as number) =>
let
y = x * x
in
y;
Then we update the sample query to call out function:
let
result = SquareNumbers.Square(7)
in
result
And it works as expected:
Exporting the connector
Once you have the connector working the way that you want, run a release build in visual studio. This will create a .mez in the bin/Release folder of your solution. Copy that file to the [Documents]\Power BI Desktop\Custom Connectors folder. You will likely have to create that folder.
Whenever you open Power BI Desktop, it will recognize the connector but won’t let you use it because of security settings.
To get around this, go into the options for Power BI Desktop and then security. Under security, select “Allow any extension to load without validation or warning.” Then Restart Power BI Desktop.
Now we can see it is available in our list of connectors.
By default it will call the Contents function:
But we can easily modify the M code to call our squared function as well.
Which will give us the output we expect.
What next?
If you are interested in going deeper with Custom Data Connectors, such as adding a navigation view or query folding, check out the TripPin tutorials.
