Power Query custom data connectors, including certified connectors, can consists of multiple components which are distributed using one of three file formats. This post attempts to (unofficially) document the basics of these formats.
As is the trend in the Microsoft world, typically an archive file is used, with the connector’s components stored as individual files inside the archive.
Connector Components
Required
- .m file
- Only one allowed.
- Normally named after the extension, but technically file name doesn’t matter; just must have a “.m” extension.
- Contains a section document defining the connector/code library being shared.
Optional
- resources.resx + resources.LANG.resx file(s)
- Used to define string values outside of the section document (i.e. outside the .m file) for ease of translation.
- A connector built for your internal use might contain just a default resources file (
resources.resx) while a publicly distributed connector supporting multiple languages will normally contain a language-specific resources file per supported language (e.g. aresources.en.resxfor English, aresources.es.resxfor Spanish, etc.) as well as possibly a possibly a default file. - When Power Query’s
Extension.LoadStringis asked to resolve a value (e.g.Extension.LoadString("ButtonLabel")), it attempts to look up the specified value first in the resource file corresponding to the currently-used language then falls back to looking in the default resource file.
- .png files*
- Typically used to provide the connector’s icons for display in Microsoft Power BI’s Get Data user interface.
- .pqm files*
- For organization’s sake, the connector’s M code can be segmented into multiple files. The typical pattern is to give these additional code files the “.pqm” extension
- My guess: pqm stands for Power Query Module.
- For organization’s sake, the connector’s M code can be segmented into multiple files. The typical pattern is to give these additional code files the “.pqm” extension
- Other files*
- As needed by the connector.
* To Power Query, there is nothing special about these files or their extensions; to it, they are just arbitrary files packaged with the connector. The responsibility for doing something special with these files lies with the code in the connector’s section document (in its .m file). For example, the expression in the section document defining the extension’s icons can be coded to read them out of the .png files. Similarly, code in the section document can import the contents of the .pqm files, when and where desired (example).
Connector Packaging
Three package formats are supported:
- mez
- An ordinary Zip file containing the connector’s components.
- Example: Take the connector component files and Zip them up using your favorite Zip file creation tool.
- pqx
- The connector’s components are packaged into an Open Packaging Conventions (OPC) Zip file.
- Example: Take the connector’s component files and package them using System.IO.Packaging.
- Included with the Open Packaging Conventions is a mechanism for applying a digital signature. This gives format pqx an advantage over format mez: pqx connectors can be digitally signed.
- Signing is a key part of distributing third-party connectors (both privately distributed third-party connectors and certified connectors shipped directly by Microsoft with Power BI).
- Certified connectors are signed by Microsoft so are always shipped in pqx format.
- m
- Just the plain text section document file in literal text form (no archive file used, so no additional files can be included).
- Example: Open your favorite text editor, enter a M section document, then save the file.
- Not normally used. Instead, formats pqx and mez are preferred. The latter two enable the connector to contain multiple components—a typical pattern but not a feature supported by format m.
File Extensions
Microsoft Power BI Desktop expects a connector to have one of the following file extensions: .pqx, .mez, .m or .pq
Interestingly, the packaging format used and the connector’s file extension do not need to align. Any of these extensions can be used with any of the connector packaging formats (so a mez packaged connector can technically be saved as having .pqx extension, a pqx format connector can be saved in a .pq file, etc.)
Recommendation: To avoid confusion and to increase chances of future compatibility, name your files with the extension corresponding to their format (e.g. name a pqx file with extension .pqx, etc.)
Format pqx Tooling
Microsoft provides a tool which converts from mez to pqx and (optionally) signs pqx files. However, since pqx is just an OPC Zip file, it is also acceptable to create and sign these files using your own tooling.
Either method allows for automation possibilities. Imagine a CI/CD pipeline that automatically bundles up a Git repository’s files into a properly structured pqx OPC Zip file with a gated release process that then signs the pqx file….
Observations
- Since custom connectors ship in a consumer-readable format, code and content you include inside a connector can be freely viewed by others. Don’t put things you want kept secret in your connectors!
- The certified connectors that ship with Power BI can be referenced to understand how they work—helpful when debugging the behaviors of a specific connector as well as providing a study resource for learning about connector development. Just keep in mind that, while certified connector code is freely viewable, this doesn’t mean it is free to reuse: copyright laws still apply.
- Certified connectors are typically installed in:
%LocalAppData%\Microsoft\Power BI Desktop\CertifiedExtensions%UserProfile%\Microsoft\Power BI Desktop Store App\CertifiedExtensions
- Certified connectors are typically installed in: