Passing a function as a parameter

R

RB160458

New member
Joined
Dec 19, 2019
Messages
8
Reaction score
0
Points
0
Excel Version(s)
Excel 365
so I have a query with the line:

Table.SelectRows(Source, each ([Region] = "Asia") )

I know how to make a country name or a file name or folder into a parameter but here I would like to replace the entire function

each ( [Region]="Asia"

with a parameter so that I can add additional selection criteria or even change the column.
 
Try something like this...

Using this table, named Table1
Code: 
Region                 Name
Asia                   Alpha
North America          Bravo
South America          Charlie
Europe                 Alpha
Asia                   Bravo
North America          Charlie
South America          Alpha
Europe                 Bravo
Asia                   Charlie
North America          Alpha
South America          Bravo
Europe                 Charlie
Asia                   Alpha
North America          Bravo
South America          Charlie
Europe                 Alpha

And this table, named Table2
Code: 
ColName         SearchVal
Region          North America

These Power Queries select Table1 rows
where the column referenced in Table2 contains the search value in Table2

Query Name: ColToUse
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    SetDataTypes = Table.TransformColumnTypes(Source,{{"ColName", type text}, {"SearchVal", type text}}),
    ColName = SetDataTypes{0}[ColName]
in
    ColName

Query Name: SearchVal
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    SetDataTypes = Table.TransformColumnTypes(Source,{{"ColName", type text}, {"SearchVal", type text}}),
    SearchVal = SetDataTypes{0}[SearchVal]
in
    SearchVal

Query Name: Table1
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetDataTypes = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Name", type text}}),
    FilterRows = Table.SelectRows(SetDataTypes, each Text.Contains(Record.Field(_, ColToUse), SearchVal))
in
    FilterRows

In that example, the Table1 query returns rows where the Region column contains "North America".
(I'm assuming you'll have your own approach to setting the variables for column name and search value.)

Is that something you can work with?
 
Thanks Ron,
this looks good.
I will check it out.
Richard
 
Sorry if this is being picky, but I would combine it all into one query Table1, and dispense with the other two. Makes it all more meaningful and maintainable IMV

Code: 
let
    Parameters = Excel.CurrentWorkbook(){[Name="Table2"]}[Content]{0},
    ColToUse = Parameters[ColumnName],
    SearchVal = Parameters[SearchVal],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetDataTypes = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Name", type text}}),
    FilterRows = Table.SelectRows(SetDataTypes, each Text.Contains(Record.Field(_, ColToUse), SearchVal))
in
    FilterRows
 
Perhaps in small one-off situations.
In my experience, for complex scenarios, many all-in-one solutions are actually more difficult to maintain.

My preference is to separate queries into 4 groups:
-Functions
-Data_Sources
-Staging
-Output

In that example, it's easier to edit the parameter sources if they are isolated, eliminating the need to edit each query that uses them.
 
I wasn't advocating all-in-one solutions in every case, but this one. I find it hard to imagine that you would have a lot of queries pulling in that parameter info.
 
This is all very useful and valuable.
Meanwhile I have been doing a bit of research and what I originally had in mind was being able to construct a text string along the lines of
each [Continent] = "Asia" and passing it to a function like Table.SelectRows(table, string)

it might be that I can pass the string to a function or use Function.Invoke or even Expression.evaluate in order to make it work.
Surely if I can replace "Asia" with a parameter then I can replace the entire function with a parameter?
 
"Asia", being a simple text string, is the easy part.

The more difficult part is injecting a column reference into the Table.SelectRows statement.

The below code is a "little" simpler in that it matches the value...where my original post matched where the target field contained the search value anywhere in the text (but couldn't reference a numeric column).

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetDataTypes = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Name", type text}}),
    FilterRows = Table.SelectRows(SetDataTypes, each Record.Field(_, ColToUse) = SearchVal))
in
    FilterRows

That code has the added benefit that, if the data type of the SearchVal column of Table2 is set to "any"...
you can reference a numeric field and match a numeric value.

Not knowing the complexity of your actual situation, it all depends on your needs.

I hope that helps.
 
Thank you once again Ron.
I do like both of your suggestions.
This one appeals to my inner geek and the former is probably more practical for the users that I have in mind.
 
So the plot thickens.
If I create a function MyFunction and pass a string MyString ='[Field1] = "Asia" & [Field2] = "Jan"' then can I use Expression.Evaluate inside the function to return true or false and then
invoke the function with: each MyFunction(MyString)?
 
this is looking very useful:
=Expression.Evaluate([formula],Record.Combine({[_=_],#shared}))

or
=Table.AddColumn(#"Changed Type", "Result", each Expression.Evaluate( [Equation], Record.Combine({[_=_],#shared}) ), type logical)
 
Technically, you *can* use the Expression.Evaluate statement...but, IMHO...it's more trouble (in this instance) than it's worth.
In my attached file, I use a custom fnGetParameter function to read params from a table and I include two approaches. One uses Expression.Evaluate. The other uses derived object references. I think you'll agree that the derived object approach is much easier to understand and maintain. Also, without somewhat complex conditional processing, the Evaluate approach is tripped up by numeric references.

Evaluate approach:
Code: 
let
    Source = Table1,
    dblquote = """",

    MyText = "each [" & ColToUse_1 &" ] = " & dblquote & SearchVal_1 & dblquote 
            & " and [" & ColToUse_2 &" ] = " & dblquote & SearchVal_2 & dblquote,

    FilterRowsUsingParams = Table.SelectRows(Source,  Expression.Evaluate(MyText))
in
    FilterRowsUsingParams

Object Reference Approach:
Code: 
let
    Source = Table1,
 
    FilterRowsUsingParams = Table.SelectRows(Source,  each 
            Record.Field(_, ColToUse_1) = SearchVal_1 and 
            Record.Field(_, ColToUse_2) = SearchVal_2)  
in
    FilterRowsUsingParams

Does that help?
 

Attachments

  • FilterTableByColAndValFromParams v1.xlsx
    23 KB · Views: 11
You must log in or register to reply here.
Back
Top