Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
sean_w
Frequent Visitor

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):

  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...

 

2 REPLIES 2
CNENFRNL
Community Champion
Community Champion

Turn those reusable steps to user-defined functions.

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

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors