Excel Power Query - part of query is build in named range

[oBi1]

Hi all - hopefully someone can help with this one:

Goal:

Allow users to dynamically say what they want to filter (what columns and what text).

More detailed description:

E.g. I have identified 7 columns they might want to filter on. In Excel I created a small table where they can select the column name (aligned with the column names in the query editor) and then they can add a piece of text they want to filter on (via Text.Contains).

I then use concat function in an Excel range to generate a text string exactly as it would look int he query editor.

Say they want to filter on Column2 for text containing "BLX" and in Column4 for text containing "COP"

Hard-coded query works flawlessly: excerpt from query

==> Table.SelectRows(#"Changed Type1", each Text.Contains([Column2], "BLX") and Text.Contains([Column4], "COP"))

What doesn't work:

My Excel worksheet cell - named range 'DataCut' - contains this text part as a result of my formula from user's input:

"each Text.Contains([Column2], "BLX") and Text.Contains([Column4], "COP")"

I import the named range into the query and call it dataCut (via Excel.CurrentWorkbook bla bla) --> confirmed shows same text

I then try to create a dynamic query by substituting the text in the hard-coded query with my imported string 

==> Table.SelectRows(#"Changed Type1", dataCut)

ERROR :  Expression.Error: We cannot convert the value "each Text.Contai..."to type Function.

What did I try - but all fail:

1) Keep 'each' hard coded and generate named range without it:

==> Table.SelectRows(#"Changed Type1", each dataCut)

2) Force evaluation to a Function type by using Expression.Evaluate(dataCut, [type Function = type Function])

For policy specific reasons I need to avoid VBA unfortunately...

Anyone has any smart ideas?

[Vijay_A_Verma · Accepted solution]

If you want to do it through Expression.Evaluate - See the working here. Solution file uploaded to https://1drv.ms/x/s!Akd5y6ruJhvhuXNG0SVHKDiCi9au?e=GdwcUz 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Name", Int64.Type}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", type text}}),
   txt = "Table.SelectRows(#""Changed Type1""," & dataCut{0}[dataCut]&")",
   Result = Expression.Evaluate(txt,[Changed Type1=#"Changed Type1",Text.Contains=Text.Contains,Table.SelectRows=Table.SelectRows])
in
    Result

datacut code

let
    Source = Excel.CurrentWorkbook(){[Name="dataCut"]}[Content]
in
    Source

[oBi1]

This worked. It appears I got the syntax around Evaluate wrong. This helped a lot, especially around the environment operators.

[AlexisOlson]

I think this might be possible using Expression.Evaluate but a better approach would be to load in "BLX" and "COP" as separate text parameters (like you did with dataCut).

Then you could write:

Table.SelectRows(
    #"Changed Type1",
    each Text.Contains([Column2], TextParam1) and Text.Contains([Column4], TextParam2)
)

[oBi1]

Thank you Alexis for your time in answering my question. THis works for some cases we have where it is predictable what columns they need. The other answer provided enabled us to use Excel to generate a fully dynamic query (part).

[AlexisOlson]

Understood. There certainly are situations where Expression.Evaluate is needed. Personally, I use it more as a method of last resort since most situations can be handled more cleanly without it.