This will be the first of a many-part series going over Power Query’s M language, and how we can use that language to create our own custom data connectors under the “Get Data” tab in Power BI Desktop.
We all love tools that do most of the work for us. That’s the point of tools: to make our lives easier. However, sometimes you have to get your hands dirty and write some code. Today’s blog post is an overview on the basics of creating a custom data connector for Power BI, using Power Query’s “M” language in Visual Studio. Let us start with the setup
- Installation of, and some experience with Visual Studio. I’m using Visual Studio 2017 for this, but you can probably use VS2015 just as well.
- The latest version of Power BI Desktop. You are also going to need to tick the box in File → Options and Settings → Options → Preview Features → Custom Data Connectors. You should probably tick most of these anyway; they add a lot of awesomeness to the product.
Setting up our first project:
Get the Power Query SDK from the Visual Studio Marketplace. (Tools → Extensions and Updates… → Online)
Start a new Data Connector project. (File → New → Project → Installed Templates → Power Query)
The anatomy of a Data Connector project:
- “ProjectName.pq” file
- Where all of the magic happens. All of your functions and logic are here, and the function(s) annotated with your Publish attribute are going to show up in your “Get Data” list.
- “ProjectName.query.pq” file
- Your test file. When you debug in Visual Studio, this is going to be the entry point for testing functions from your .pq file.
- Connector icons
- A list of eight PNG files that represent the various icons for your connector. These are created automatically with the Power BI logo, and you can swap them out with your own icons if you want.
Show the M Query Output window, if it isn’t already. (View → Other Windows → “Mashup” [I have no idea why they called it this.])
Type a quick line of M code into the “.query.pq” file, and hit F5 to see the results in your “M Query Output” Window.
Let’s reverse the change we made to the “.query.pq” file and jump back to our actual data connector logic and put it there (don’t worry about the auto-generated boilerplate for now, and ignore that red line; it seems to be a bug for now):
Notice how we’re calling the “World_Statistics.Contents” function with our test file by invoking the name followed by parentheses. Once we hit F5 and see that same output, we actually have a data connector we can use in Power BI! Let’s get that data connector in the right folder, and test it out:
• Right-click your project, and select “Open Folder in File Explorer”
• Navigate to “Bin\Debug”. You should see a single file with your project name and the extension “.mez”.
• Copy that file into “C:\Users\[yourUserName]\Documents\Microsoft Power BI Desktop\Custom Connectors”
• Note: You will probably have to create the last two folders in that path. Make sure you create them exactly as spelled here.
Now start up Power BI, go to “Get Data”, and scroll all the way down. You should see “World Statistics (Beta)” as an option. If you connect to it, you will get exactly what we coded into our “.pq” file: a single list of integers from 1 to 42.
And that’s it! We have created a custom data connector that can be used in Power BI Desktop. At this point, you have all of the boilerplate done; you can get right to writing your own logic in M, such as pulling from public APIs. Lucky for you, that is exactly what the next part of this series of posts is going to be about.
Read Power BI Custom Data Connectors, Part 2: Working with Public JSON APIs.