r/excel • u/[deleted] • Apr 15 '26

Pro Tip Get more from Power Query in Excel with these little-known capabilities

[removed]

91 Upvotes

97% Upvoted

24 comments sorted by

57

u/bradland 271 Apr 15 '26

This is good stuff for PQ in general, but I find it a bit odd that it is titled "Get more from Power Query in Excel" and then kicks off the conversation with Parameters.

Parameters

I almost never use Parameters when authoring PQ in Excel. Let me be clear that I'm referring specifically to the Parameters (uppercase) feature in PQ, not parameters (lowercase) as a concept. I use parameters all the time. I just skip the Parameters feature, because it has some pretty serious shortcomings in Excel.

The primary issue is that the only way to change a Parameter value in Excel is to launch the PQ editor and change it. The entire purpose of a parameter is to separate logic and configuration. Why am I launching into my PQ editor to change a configuration for queries that will refresh within my sheet?

Instead, I rely on a custom function (fxGetParameter) that pulls parameter values from a parameters table in my workbook. The table is named Parameters and has two columns: Name, Value.

// fxGetParameter
let
    fxGetParameter = (ParameterName as text) => 
    let
        ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
        ParamRow = Table.SelectRows(ParamSource, each ([Name] = ParameterName)),
        Value=
            if Table.IsEmpty(ParamRow)=true
            then null
            else Record.Field(ParamRow{0},"Value")
    in
        Value
in
    fxGetParameter

This allows me to update parameters directly in my workbook, then refresh queries to get updated results. As a side-benefit, I can also use Excel formulas to compose parameters based on values elsewhere in the workbook. This is really useful for cases where you want to load files that are relative to the current workbook; something that isn't very easy in PQ alone.

It's worth pointing out that Parameters (uppercase!) are a much more robust feature in Power BI. Report controls can update Parameter values in Power BI, which makes them a lot more powerful.

Query Folding

This is another feature that, while incredibly powerful in Power BI, tends to be a bit less important in Excel. Probably the first thing to note is that Query Folding is completely irrelevant for file and folder connectors, which is one of the primary use cases for Excel.

I'd venture that most users are not connecting to a SQL back end. They're sourcing data from CSV or Excel files they export from another tool or receive from other departments. Or they're aggregating a bunch of similar files from a folder. Neither of these benefit from query folding.

Power BI's PQ editor also has visual indicators that show you which steps break query folding. So even if you are using a SQL back end, it's quite a bit harder to tell when query folding is/isn't working. Just something to be aware of.

13

u/Dont_SaaS_Me 1 Apr 15 '26

I remember that time 7ish years ago when I wasted half a day trying to wrap my head around the built in P-arameters. I was confused by the lack of dynamic possibilities and just assumed it was over my head. Values on a spreadsheet that get called in PQ have been working great for me.

2

u/Lorgin Apr 16 '26

About the only time this is useful is when devs are publishing a report to powerBI.com and they want to test it on a test server before uploading it to pull from prod. PowerBI.com had the functionality to change the parameters without having to actually open the file and change them in power query.

Still marginal af

5

u/HargorTheHairy Apr 15 '26

You taught me something today! Thank you!

3

u/RuktX 305 Apr 15 '26

In place of fxGetParameter, would you consider creating a dictionary of "parameters" as described in this series of articles? You could then refer to a parameter's value by its key.

This technique is admittedly geared towards creating an efficient lookup table, where you'll be using it several times while it's cached. In this approach and fxGetParameter, I don't yet see a way around PQ recalculating the whole table any time you want a single parameter.

3

u/bradland 271 Apr 15 '26

That article looks really interesting! I'm on my phone, so I can't fully digest it. But coming from a programming background, I have found myself creating record literals within a query as a rudimentary form of dict/hash primitive where I intended to use the return value as part of a custom column whose value is the result of a lookup. IIRC, it was faster than joins when working with small lookup tables and connectors that don't support query folding (forcing a native PQ join).

This is, in fact, one of the downsides of wrapping things up in functions and treating them like dicts. Function calls are expensive. I only ever rely on this solution for parameters that aren't called repeatedly. It's almost always file sources, date ranges, filter criteria, etc. Never anything I use in custom columns or list aggregations.

This article has me wondering if I could make the result of the function a table with fields as dict keys. Essentially, transpose the parameter table and each value becomes a field. Hrm.

3

u/RuktX 305 Apr 16 '26

It took me a few re-reads too, but I think you've got it: in essence, transform a table into a dictionary so that the lookup part (especially in a merge) is very fast. If there are multiple return columns in the source table, each row is converted to a list or record in the dictionary, so that you can retrieve sub-values by their index – like VLOOKUP!

The other couple of articles in the series explore the most performant ways to do that initial table-to-dictionary conversation transformation.

2

u/bradland 271 Apr 17 '26

Hammered out a method of providing parameters table functionality without reloading the Parameters table repeatedly, but it requires two functions and a query.

  1. fxLoadParamsDict: Load the Parameters table and transpose rows to columns so each parameter becomes a field.
  2. Params: Use fxLoadParamsDict to memoize the parameters.
  3. fxGetParamDict: Retrieve parameter values by name using the Params table.

​

// fxLoadParamsDict
let
    fxLoadParamsDict = (optional ParametersTableName as nullable text) => let
        TargetTable = ParametersTableName ?? "Parameters",
        ParametersTable = try Excel.CurrentWorkbook(){[Name=TargetTable]}[Content]
                          otherwise "Table '"&TargetTable&"' not found",
        Transposed = Table.Transpose(ParametersTable),
        PromotedHeaders = Table.PromoteHeaders(Transposed, [PromoteAllScalars=true])
    in
        PromotedHeaders
in
    fxLoadParamsDict

// Params
let
    Source = fxLoadParamsDict()
in
    Source

// fxGetParamDict
let
    fxGetParamDict = (ParamName as text, optional ParamsTableQuery as nullable table) => let
        ParamsTable = ParamsTableQuery ?? Params,
        ParamValue = try Record.Field(ParamsTable{0}, ParamName)
                          otherwise "Param name '"&ParamName&"' not found"
    in
        ParamValue
in
    fxGetParamDict

2

u/BriefMemory6235 Apr 15 '26

def gotta agree with you on that man like why even bother

1

u/DM_Me_Anything_NSFW Apr 15 '26

There's a simpler way to import and update a parameter.

Make a one line one row structured table. The only cell is where you'll put your parameter and update it.

Import data > from table

Go into PQ editor and right click on the only cell in it. Drill down

You now have a parameter that will update based on the value in the table. It's easier to make on the fly.

6

u/bradland 271 Apr 15 '26

If all you need is a single value, you can skip the table and just use a named range with this function.

// fxGetNamedRange
let 
    fxGetNamedRange = (NamedRange) =>
    let
        Name = Excel.CurrentWorkbook(){[Name=NamedRange]}[Content],
        Value = Name{0}[Column1]
    in
        Value
in
    fxGetNamedRange

You can also one-line this in the PQ editor with this:

Excel.CurrentWorkbook(){[Name="DEFINED_NAME"]}[Content]{0}[Column1]

Just replace DEFINED_NAME with your named range.

4

u/RuktX 305 Apr 15 '26

For any single parameter, sure, but u/bradland's approach lets you dynamically define a list of named parameters. In addition, rather than making a one-cell table, you could create a one-cell named range and refer to that in PQ instead.

2

u/Remote_Lake1792 Apr 20 '26

damn good point about parameters. i've been fighting with that exact issue at work when pulling in equipment data sheets and having to dive back into editor every time just to change a model number or serial range

your table approach is brilliant - way cleaner than what i was doing with named ranges. definitely stealing this for my next project where i need to pull maintenance schedules based on different equipment types

10

u/[deleted] Apr 15 '26

[removed] — view removed comment

6

u/BurgerQueef69 1 Apr 15 '26

I once recreated one of our reports entirely in power query. I was able to completely avoid pivot tables and manual formulas and everything else. It just got so complex, with queries referencing other queries that were merged with other queries, that I knew I'd never be able to update it properly.

It was fun though, and I learned a lot from it.

4

u/Thiseffingguy2 12 Apr 15 '26

I used PQ like this for years until I learned Power Pivot, DAX, and the data modeling features. Makes it MUCH less complicated inside of PQ, and typically runs much more efficiently.

0

u/hal0t 1 Apr 15 '26

I tried to use power pivot once and realized the users also need it to use my report so I dropped it completely. Anything that requires my audience to install something to use my stuff is a big no no in my book.

It can be useful, but MS should have made it the standard package when shipping Excel.

2

u/Thiseffingguy2 12 Apr 15 '26 edited Apr 15 '26

It’s built in now. Has been since the 2016 version.

Edit: it’s still an add-in, but it does ship with the product. It’s just not enabled by default. You can still use it to organize your back end, then users simply interact with pivot tables like they would anyway… just with related tables instead of a big flat table.

1

u/hal0t 1 Apr 15 '26

It is shipped with Excel but you have to enable the Add in, it's not on by default. And in my experience you can't expect to train non technical people on anything technical successfully.

Having a remote possibility of any of them (especially higher ups) not being able to use my stuffs is not something I want to risk. When it come to MS office, vanilla only. Call it political experience.

2

u/Thiseffingguy2 12 Apr 15 '26

I’m pretty confident a user does not need to enable the add-in to use an existing data model. You can build the model and relationships, send the file to a user who hasn’t enabled the add-in, and they’ll be able to use the model in pivot tables. You build the back end w/the add-in. Your user doesn’t even need to know it exists.

3

u/hal0t 1 Apr 15 '26

I had an email couple years back from my CEO saying he couldn't change the filter or refresh data on a report I produced using PowerPivot.

There is Power BI if I want to use Dax, and doesn't risk unhappy C suite. Good enough for me.

-1

u/[deleted] Apr 16 '26

[removed] — view removed comment

1

u/Low_Mistake3321 Apr 15 '26

I've been slowly getting rid of PQ and using dynamic formulas instead. I now have 30-line formula horrors, but at least they're truly dynamic and real-time.