Forum Discussion
Power Query equivalent of PERSONAL.xlsb for queries, functions, etc.
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):
- Am I simply not finding the details on how to do this?
- Is this a feature that's currently in the works?
- 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:
9 Replies
CNENFRNLCommunity Champion
Turn those reusable steps to user-defined functions.
(arg1, arg2, ...) => let step1 = "...", setp2 = "..." ... setpn = in stepnYes - 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.
KeezzHelper I
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....- 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!
