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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

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