My Own #Shared M Library: Take 1

I’ve been trying to write an M extension. I should distinguish this by saying I am not trying to write a connector. I really just want to write a shared M library that I can refer to and import once. Before anyone starts, I realize I can just make a template and put all of my M in there and reuse that. I am trying to make something that I can easily share with others.

Once I got started with the extension, I was surprised at how easily it worked. I was able to write a .PQ file, place it into my custom extensions folder for Power BI, and use the functions. It was really cool. But then after reading some articles sent my way, I realized that in order to deploy it I would need (I believe) a gateway to be able to even use it in Fabric.

There goes that idea.

But then I remembered that I am stubborn. I have been playing around with Function.From() and Table.FromRecords() to mimic the #shared keyword. If you did not know, if you use the #shared keyword, you will see all of the functions defined in your file, amongst other things.

With these functions, I was able to put something rough together using this sort of framework:

The gist of it is to define functions as steps and then reference those steps in a record, which is then turned into a table at the end.

Here is what I have so far:

pqshared.pq (github.com)

It’s long, but it pulls together a bunch of math/stats functions I have played around with.

Output

Pasting this monstrosity into Power Query provides you with each function as a distinct step and then a table at the end with all of them:

Why did I do it this way? I thought maybe that being able to click each step and see each function would be helpful. Additionally, setting up the code was easier for me this way. If this is something I think is ultimately useful in the long-term, I may use Python and Jinja2 to template out the generation of the larger query.

Invoking

This is where this version is a little clunky. How the heck do you invoke the function? For now, I thought maybe another helper function would be easiest:

(t as table, func as text) =>
let
  Source = Table.SelectRows(t, each _[Name] = func)[Function]{0}
in  
  Source

With this function, you could reference any function in the library and even pass in parameters as the same time. Here is an example:

In this example, I passed in the number 123 as a parameter to the pqmath.Benford function. It’s cool that you can do this much like how you use Lambda functions in Excel.

Wrapup

Is this a good thing? I think it could be, but I am not sure it is all fleshed out yet. I am really trying to figure out ways to easily package and share out libraries of M code. People reach out at work from time to time and I also do a lot of work with M, so maintaining a “single source of truth” for myself that is easily portable (and in Github) seems like something well worth my time.