Column value of one table to act as filter expression for other table

[Prashant_Saroj]

Suppose in table1, I  have a column named 'predicate'. It contains boolean expression of the form 'column_name_of_table2 == value', I want to drill through table1 and show rows of table2 which satisfies the boolean expression.

Example- 

Table1

Predicate

student == 'stephan'

course == 'CS101'

Table 2

Student	Course
stephan	CS213
not_stephan	CS101

When user click on the first predicate I want to show all rows related to stephan in Table2. How do I achieve this ?

[v-shex-msft · Accepted solution]

HI @Prashant_Saroj,

As amitchandak said, 'Expression.Evaluate' function should help with your requirement. But it required you to do some additional steps.

1. Replace operator with power query operators:

Power Query M formula language / Operators 

2. Replace your field names with raw table field names. (notice: power query is case-sensitive of field names)

transformed 'Condition' table

3. Package field names with '[]' characters and concatenate your conditions with logic operator 'or' and add 'each' keyword at the start of the merged condition string.

    ConditionString = "each "&Text.Combine(List.Transform(Condition[Predicate],each "["& Text.Insert(_,Text.Length(Text.Split(_," "){0}),"]"))," or ")

merged condition string

4. Add a filter step in your raw table steps, use Expression.Evaluate function to invoke processed condition string.

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", Expression.Evaluate(ConditionString,#shared))

Full Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi5JLchIzFPSUXIONjI0VorViVbKyy+JRxY3NDBUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Student = _t, Course = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Student", type text}, {"Course", type text}}),
    ConditionString = "each "&Text.Combine(List.Transform(Condition[Predicate],each "["& Text.Insert(_,Text.Length(Text.Split(_," "){0}),"]"))," or "),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", Expression.Evaluate(ConditionString,#shared))
in
    #"Filtered Rows"

Regards,

Xiaoxin Sheng

[amitchandak]

@Prashant_Saroj , refer if this can help

https://blog.crossjoin.co.uk/2015/02/06/expression-evaluate-in-power-querym/