Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Passing a function as a parameter

  1. #1
    Seeker RB160458's Avatar
    Join Date
    Dec 2019
    Posts
    8
    Articles
    0
    Excel Version
    Excel 365

    Passing a function as a parameter



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Conjurer Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    108
    Articles
    0
    Excel Version
    2013, 2016, O365
    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?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  3. #3
    Seeker RB160458's Avatar
    Join Date
    Dec 2019
    Posts
    8
    Articles
    0
    Excel Version
    Excel 365
    Thanks Ron,
    this looks good.
    I will check it out.
    Richard

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,699
    Articles
    0
    Excel Version
    O365
    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

  5. #5
    Conjurer Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    108
    Articles
    0
    Excel Version
    2013, 2016, O365
    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.
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,699
    Articles
    0
    Excel Version
    O365
    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.

  7. #7
    Seeker RB160458's Avatar
    Join Date
    Dec 2019
    Posts
    8
    Articles
    0
    Excel Version
    Excel 365
    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?

  8. #8
    Conjurer Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    108
    Articles
    0
    Excel Version
    2013, 2016, O365
    "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.
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  9. #9
    Seeker RB160458's Avatar
    Join Date
    Dec 2019
    Posts
    8
    Articles
    0
    Excel Version
    Excel 365
    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.

  10. #10
    Seeker RB160458's Avatar
    Join Date
    Dec 2019
    Posts
    8
    Articles
    0
    Excel Version
    Excel 365
    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)?

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •