Enabling Extensions

Power Query supports a wide array of different data source connectors for accessing and retrieving data from a variety of different data sources. In fact, Power BI Desktop natively supports almost 200 different data source connectors, most of which have been covered in Chapter 3, Accessing and Combining Data. In addition, many of these data source connectors are generic for various data format standards and protocols such as the Open Data Protocol (OData), Open Database Connectivity (ODBC), JavaScript Object Notation (JSON), Extensible Markup Language (XML), and Parquet. Support for these common standards and protocols significantly expands the potential data sources for Power Query to thousands, if not tens of thousands of potential sources.

Even with all the data sources already supported by Power Query, there is still a need to support additional data sources. In particular, various web services often provide an application programming interface (API) that...

Technical requirements

You will require the following to complete the tasks in this chapter:

• Visual Studio Code
• Power Query SDK

What are Power Query extensions?

In short, Power Query extensions are primarily pieces of M code that add to the global environment. Refer to Chapter 7, Conceptualizing M, to understand more about the global environment. Extensions are primarily used to create custom data connectors but can also be used to create a library of reusable custom functions.

These extensions come in four different file types (file extensions):

• m: This is a simple text file containing M code.
• Pq: This is a simple text file containing M code.
• mez: This is a standard ZIP file containing custom connector files. Can use a standard ZIP file and rename it from file.zip to file.mez.
• pqx: This is an Open Packaging Conventions (OPC) ZIP file containing custom connector files. Typically packaged using the System.IO.Packaging .NET library, this packaging format allows the digital signing of the file and is the format for Microsoft-certified connectors.

As noted, .mez and ...

Preparing your environment

To prepare your environment, you must download and install two software components:

• Visual Studio Code
• Power Query software development kit (SDK)

In addition, the custom connector example covered in this chapter connects to Discord, a popular voice and instant messaging social platform. We will use the Discord API to connect to a Discord server and retrieve information. To use Discord, we will also want to install and configure two additional components, namely:

• Internet Information Services
• Discord

Let’s look at how to get these software components installed and configured.

Getting Visual Studio Code

There are currently two versions of Power Query: a version released in 2017 that works as an extension for Visual Studio 2017 and 2019 and a new version (currently in preview on the publication date for this book) that works with Visual Studio Code.

For this book, we will be working with...

Creating a custom connector

With Visual Studio Code, the Power Query SDK installed, our local environment configured with IIS, and Discord elements created and configured, we can now create a custom connector for Power Query.

The full code for the custom connector for this book is available in the GitHub repository found here: https://github.com/PacktPublishing/The-Definitive-Guide-to-Power-Query-M-/tree/main/Chapter%2016. You can either download the TDGTPQM_Discord.zip file from Chapter 14 in the repository, extract the files, copy and paste the connector folder somewhere on your PC, and then open the folder in Visual Studio Code ( File | Open Folder…), or you can follow along with the rest of this section where we build the connector from scratch.

In either case, you will be well served by reading the code explanations throughout the rest of this section. The rest of this section assumes that you are creating the custom connector from scratch.

The steps to create...

Installing and using a custom connector

To do this, please follow these steps:

1. With your project open in Visual Studio Code, save your work by clicking File | Save All in the menu.
2. Expand the POWER QUERY SDK section in the EXPLORER pane.
3. Click Run TestConnection function and ensure no errors are displayed.
4. Make sure that the TDGTPQM.pq file is selected in the Explorer pane and then click the Evaluate current file. This time, the PQTest result tab should display the following information:

   

   Figure 16.10: TDGTPQM.pq evaluation output

   As you can see, this is the top level of how our navigation will be displayed in the Navigator dialog of the Power Query UI and directly corresponds to the specifications of our objects expression from our TDGTPQM_Discord.Contents function.

5. Click the bin\AnyCPU\Debug folder link in the EXPLORER pane to expand this folder.
6. Right-click the TDGTPQM_Discord.mez file and choose Reveal in File Explorer...

Summary

In this chapter, we dove into the world of extending Power Query’s capabilities by adding a custom function library and creating a custom connector. Power Query is a powerful data transformation and integration tool, but its true potential can be unleashed by developing your own connectors to access specialized data sources or APIs. This allows you to bring in data from unique systems or services that are not natively supported by Power Query.

We began by preparing a development environment, including configuring Visual Studio Code, the Power Query SDK, and elements of Discord, our target data source. Next, we created and explained the base template for custom Power Query extensions. We then coded and configured OAuth authentication for our custom connector. Additionally, we wrote the custom M code for retrieving data from the Discord API and presenting a navigation experience for the Navigator dialog found in Power Query UI. Finally, we demonstrated how to install...