Power Query equivalent of PERSONAL.xlsb for queries, functions, etc.

[sean_w]

Problem:

I often find that I want or need to reuse a Power Query function or approach in one Excel workbook that I've used in a previous Excel Workbook, without the hassle of "reinventing the wheel".  I know there is a solution to this dilemma with regards to Excel Macros (link below) but I haven't found or figured out a way to accomplish this with Power Query. 

Question(s):

1. Am I simply not finding the details on how to do this?
2. Is this a feature that's currently in the works? 
3. Or is there ultimately no way to accomplish this due to the inner workings of Excel Power Query?

PERSONAL.xlsb for reusing Excel Macros across any workbook:

https://support.microsoft.com/en-us/office/create-and-save-all-your-macros-in-a-single-workbook-66c9...

[Keezz]

This does not exist unfortunately.
I built a VBA macro to copy queries from and to personal.xlsb. It is a bit of a hassle to setup, but it works for me.

I have also thought about using a seperate workbook to store all librray queries in a worksheet and load them in a powerquery record using Powerquery itself. Expression.Evaluate should be able to do the trick.
Assumming you name the record M, you could call each function or query as M[Your Query Name Here].
I did a proof of concept, but never finished it. Advantage over the VBA is that you always work with the lastest version. Downside is that it is a bit of a hassle to maintain....

[dufoq3 · Accepted solution]

You can store your functions/queries in text file. Check this.

[sean_w]

Thanks for the link to the other post.  Still wish this was something that was native to the Power Query platform, but this is a great alternative.

[dufoq3]

😉

[foodd]

OMG., @dufoq3 , thank you for sharing.  And to this day, yes, @jennratten is still Awesome 🙂 

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!

[dufoq3]

You're welcome. Enjoy 😉

[Keezz]

Thanks! Also a good one! Easy to update.
I would extend it with puting all queries in a separate directory and automatically store all functions as separate fields in a record. 
I only need to copy paste 1 query in all sheets with powerquery to boot strap the function loading process.
Anyone interested in the full solution? Then I will build it.

[CNENFRNL]

Turn those reusable steps to user-defined functions.

(arg1, arg2, ...) =>
let
    step1 = "...",
    setp2 = "..."
    ...
    setpn = 
in
    stepn

[sean_w]

Yes - completely agreed and I do when it makes sense.  However when a user-defined function (UDF) is created, it only exists (natively) within the workbook it's created in.  Let's say I need to perform the same steps from the UDF but in a different Excel workbook.  I either have to:

• Save that UDF into a text file when I create it in the original workbook, find that file, open & copy the text, create a new blank query, and paste in the text.  Additionally, if I make any alterations to the UDF in the original workbook, I have to keep those changes in sync with the static text file.
• Remember which workbook the original UDF was in, open the file, copy the query/UDF, and paste into the new workbook.  Same issue here - if for any reason I update the UDF in one and I need that update to apply to all instances of that UDF, I have to go find and update each manually.

The idea behind saving Excel macros to PERSONAL.xlsb is that it creates a single point for all macros you want available in other workbooks.  My question is specific to this concept, but applied to Power Query.