Sharing Power Query functions — the ‘library on Github’ way

For 1.5 years of interesting spreadsheets and related tricks I’ve (largely) neglected to post about any. I’m among a billion voices, so for the most part adding to the noise seems useless. This time I found something worth spreading.

I’ve been pretty evangelic about Excel’s shiny recent plugins (their names start with ‘Power’), and recently learned something cool about Power Query. I’d known of the ability to dynamically load M code from a text file, which seemed interesting. It was only a couple days ago that I found out Power Query also allows defining reusable functions though. This was exciting to me because it would allow the kind of modular kind of coding I liked of Excel VBA — solve a problem once, make the solution into a new function, problem solved (permanently).

I then revisited Chris’s mentioned blog post, and found this thread on how to dynamically load code for such user-defined functions from a file. This combination seemed more than significant to me, as it would bring this modular coding to Power Query as well. Find yourself using the same three functions in a row a few times? Make it a new function and save some lines each time! Beats being stuck with nothing but those regular functions.

With this in mind, I externalized a couple functions I had / could find online (yeah mostly Chris Webb’s blog) into separate files, and put them into a Github repository so others could use them as well, and hopefully add new functions. Further explanation in there.

 

Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

4 Responses to Sharing Power Query functions — the ‘library on Github’ way

    • tycho01 says:

      Thank you! :)

      In terms of activity in the Power Query field you’d no doubt do better maintaining a function library than I would, though I’m glad to get the idea out there at least.

      I think right now challenges for the Power Query community as a whole are that (1) it’s small and (2) many are probably only partly proficient in M, myself included — if I hadn’t found your blog post on how to completely expand a table dynamically, I wouldn’t have figured out how to do it. Then again, I do wonder if there might be enough others for this to work out…

      Considering right now each function needs to explicitly load any other functions it’s calling I’m still hoping for some scope solution that might render that unnecessary (thread), though I suppose that currently seems unlikely to yield a better alternative.

      • Chris Webb says:

        I suspect the Power Query team might also be thinking of ways to solve this particular problem (both inside Power BI and hopefully also outside it); I don’t think there’s a truly elegant solution to the problem of loading all the functions needed by a given function and maybe it’s better not to be too ambitious. It’s also worth pointing out Curt Hagenlocher’s comments on my post about loading code from text files – doing this could interfere with way PQ evaluates queries.

  1. tycho01 says:

    Right, I remember that comment.
    Similarly, in the Technet thread he’d mentioned passing the #shared environment to allow use of global scope functions (essential in this context) was considered unsupported functionality (or even a bug) as well. In either case, I’m not exactly sure what these will concretely mean in terms of when or how this Load function might break.
    So far for me this concept has been working though, so I’m not sure if I should feel deterred by these risks already… For now, this seems like a fair step forward to me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s