Organizing Functions in a Power BI Custom Data Connector

The more complex a Power BI Custom Data Connector gets, the more cumbersome the main Power Query file becomes. Without the ability to make a true “project” structure and include or directly reference functions from other files at this point, I wanted to find a way to keep things a little more organized.

In the series of tutorials that the Custom Data Connector team put together, there’s an example of how to include external functions at the end of Tutorial 7. Although it’s not mentioned in this short explanation, I quickly realized that a Power Query project references all files by inclusion in the final build, not the physical path structure of the project or through namespaces. As long as the file is set to be compiled into the final solution, you can reference it by name regardless of where it is actually placed in the file structure of the project itself.

Therefore, to keep my Custom Data Connector projects a little more visually organized (and to help my future self remember how functions relate), I’ve gotten into the habit of organizing my projects using folders that group common functions together.

I’m still tweaking what I call my “standard” set of folders, but between projects the concepts are the same.

The Root

I try to only keep my Resource and Power Query files in the root of the project, making it easy to find as the number of functions grows.

Icons

Next, I always move the icons into a separate folder. In the generic Power Query project template, you end up with 8 icon files taking up space and named after your main PQ file. Clutter.

Old Snippets

This folder isn’t always named consistently, but I keep a folder for functions and snippets of code I don’t need anymore, usually because I found a better way to do something. I’ve found that it can be helpful for me to refer to old examples of how the project evolved in a way that looking at the commit history can’t solve. When snippets are in a file, it’s easy to include it as a function and see it work again.

Helper/Core

I’m still trying to figure out what I really want this folder to be called, but it contains the meat of my application function logic. Inside of this folder I make sub-folders that group common functions together.

• Table – These are usually the base helper functions provided by the team at Microsoft with some custom modifications.
• Data Transformations – The heart of a data connector is about transforming data into something usable for… the user. These are separate functions that generally translate to each Query in Power BI.
• Authentication – Most data sources require some sort of Authentication. Most of these functions are based on authentication examples from the Microsoft team. I’ll tweak them as needed for the specific project.

Making it work

Whatever your specific folder structure becomes, how does this work out in practice?

Simple! Any file that you want to reference in connector code has to be set as Compile in the Build Actions. Once that is set, the file can be referenced by name only in a PQ function. If you ever forget to set a file to be included in the compiled Data Connector, you’ll quickly get a warning.

For files like the Icons, you don’t need to do anything special after setting the build action. As long as the name stays the same, the references in you Connector.Icon method will just keep working.

However, if you want to load one of the other functions, you’ll need to use the temporary workaround as described in Tutorial 7.

// 
// Load common library functions
// 
// TEMPORARY WORKAROUND until we're able to reference other M modules
Extension.LoadFunction = (name as text) =>
    let
        binary = Extension.Contents(name),
        asText = Text.FromBinary(binary)
    in
        Expression.Evaluate(asText, #shared);

With that function available, you can now load other functions with one or two lines of code. If the method requires no inputs, just initiate it to a function variable.

Table.ChangeType = Extension.LoadFunction("Table.ChangeType.pqm");

In some cases, however, your function might need access to another external function or parameter which has to be passed by reference because the scope has changed. In that case, you need to load the function first and then make it available through a second function variable that accepts the external references and any additional parameters.

TokenMethodFunction = Extension.LoadFunction("Auth.TokenMethod.pqm");
TokenMethod = (grantType, tokenField, code) => TokenMethodFunction(grantType,tokenField,code,OAuthConfig,ExchangeToken);

With this workflow, my overall Power Query file is much less cluttered and it helps bring more meaning to my source control commits when functions are broken down into smaller chunks.

The One Downside

I’d be remiss if didn’t mention the one, hopefully obvious, issue with moving files outside of the main Power Query file into .PQM files.

The lack of syntax highlighting and intellisense.

It is annoying. I’ve learned to deal with it for small changes. If I need to work through a problem, I’ll copy the function over until it’s been modified and is working, and then wire it back up through the “LoadFunction”. In reality, once a function is working, it’s rare that I’ll actively modify it.