Custom Data Connector: How to Deploy and Test

[Nolock]

Have you developed your own custom data connector and you think the work is done? Well, it is not. You must deploy your solution and test is in the Power BI Desktop and Power BI Service. And that is what I will discuss in this article.

Disclaimer

I have developed a simple data connector which makes an API call to Deutsche Bahn AG (German railways company). It was created just for this demonstration and the M-code behind is a quick & dirty solution. The intention is to show how to deploy and test a custom data connector.

Prerequisites

You have created a new data connector project in Visual Studio using the Data Connector SDK. You know all the fancy stuff like how to build a Navigation table, you also know what the Test Connection function is for, and you know what the files <yourProjectName>.pq and <yourProjectName>.query.pq are for. You have also read the documentation: https://github.com/microsoft/DataConnectors  and https://docs.microsoft.com/en-us/power-query/samples/trippin/readme or something similar.

First run

Start your code with F5 (Start Debugging) or with Ctrl + F5 (Start Without Debugging). If your data connector expects some credentials, you see the following screen at the first run.

You must choose a Credential Type. Depending on the chosen credential type there will be different fields you have to fill in.
In my case, there is only one field: Key.

By the way, both Visual Studio and Power BI Service ignore label names configured in code! On the next screen we see Key. However, I’ve set the KeyLabel as “Bahn Token”. The only place where you will see the correct label name is Power BI Desktop.

Authentication = [
    Key = [
        KeyLabel = "Bahn Token",
        Label = "Bahn Token"
    ]
]

After clicking on Set credentials, you can switch to the tab Credentials. You will see the Credential Manager with our first credential record. The credentials have not been checked, just saved! Even if you have implemented a TestConnection function, it won’t be invoked at this place!

At this point, you can press the F5 or Ctrl + F5 again and magic happens. Our custom connector is starting with an animation. When the animation disappears, we get some output. The output is an execution of an expression which we can find in the file ending with .query.pq. In my case it is BahnParkTest.query.pq. This file doesn’t contain any application code, it is there for testing purposes only. It means it is the starting point for tests.

Firstly, we invoke our navigation table which is generated in the file BahnParkTest.pq and doesn’t call any webservice. It means it appears even when our credentials are wrong.

Next, we navigate through our navigation table and get some results from the API. That’s the proof that the credentials are correct, and our connector really works.

Deployment to Power BI Desktop

Our goal: The new data connector appears in the list of connectors in Power BI Desktop.

By setting the Solution Configurations to Release and pressing Ctrl + F5 in Visual Studio, we have created a .mez file. This file can be found in the project folder in bin/Release as you can see on the following screenshot. Copy this file to “Documents/Power BI Desktop/Custom Connectors” on your machine.

Start the app Power BI Desktop, click on Get Data, go to Other and navigate to your new data connector. It should appear in the list.

However, it can happen that you don’t see it. It probably means you have not allowed connectors which are not trusted. (For your information, you can also sign a data connector to bypass this problem: https://docs.microsoft.com/en-us/power-query/handlingconnectorsigning). Don’t forget to restart Power BI Desktop.

I hope you can see your custom connector now. Click on it and fill in parameters. In my case it is only the place name.

On the next screen you see all the allowed authentication options. We have implemented only a token, therefore, there is only one. This time Power BI Desktop shows our key label “Bahn Token”. Great, it works!

On the next screen is our navigation table. When you click on “Managing spaces” a data preview appears. It means our credentials are correct and we can load data into our data model.

At this point we are done in Power BI Desktop. The data connector works, and we can publish the new PBIX file to the Power BI Service.

On-Premises Data Gateway

Before we configure the new data connector on the Power BI Service website, we must deploy the connector on a data gateway.
Open the app On-premises data gateway, go to Connectors and copy the path of a folder where the data gateway loads custom data connectors from. Navigate to the path. If you already have some custom connectors, you see them there - the .mez or .pqx files. Copy our BahnParkTest.mez file into the Custom Connector folder and that’s all.

Sometimes you must restart the data gateway service when you upload a new version of a connector. Unfortunately, I have not found out what it depends on. Therefore, I have prepared a batch which replaces the old version of the connector with a new one and then restarts the test data gateway service. Trust me, you will do that many times when you develop your first data connector.

xcopy /y "\\PATH_TO_PROJECT\bin\Release\BahnParkTest.mez" "C:\Users\XXX\Documents\Power BI Desktop\Custom Connectors"

net stop PBIEgwService && net start PBIEgwService

Power BI Service

The last part of this tutorial is how to create a new data source on our gateway. Click on the ellipsis (3 dots) next to the name of your gateway. They show up by a mouseover.

We fill in all the forms. One of them is for example the Data Source Type which you can chose from a list of all the available connectors. There are also all parameters, for example PlaceName.

Click on Add. The following screen shows up.

Focus on the line with Data Source Information. You see the parameters are stored in a JSON! And that is also the format when you read them in your custom connector!!!

To read the property PlaceName you must write something like:

DataSourcePathAsJson = Json.Document(dataSourcePath),
PlaceName = DataSourcePathAsJson[PlaceName]

You also see that the connection has been established. Well yes, but I have not found out how to test the connection in a proper way. It seems like the invocation of TestConnection cannot access Extension.CurrentCredential() and therefore we don’t have credentials at this point. I have followed the tutorial of Matt Masson on https://github.com/microsoft/DataConnectors/tree/master/samples/TripPin/9-TestConnection but never been able to bring it to life ☹

Now, go back to the settings of your dataset. We have to map an extension to a data source. As you can see on the following screenshot, there is only one data source we can choose.

Click on Apply and your dataset is configured for a refresh on a data gateway.

Because our TestConnection doesn’t work properly, please try to refresh your dataset. If it is successful, we are done. If it fails, go back to Visual Studio and hack.

That’s all for today.

[Eliasaki92]

Another awesome post - thx nolock!

[alexbjorlig]

Would you mind sharing the source code? I'm very interrested in the navigation table part - I'm having trouble only showing a preview in my connector (it loads everything when the user selects a table).

[Nolock]

Hi @alexbjorlig,

I'm very sorry for my late answer.

Nowadays, there is a very good documentation from Microsoft to this topic: TripPin 3 - Navigation Tables | Microsoft Docs I'd recommend to start there.