How to use SQL query criteria stored in a column to return data using a function or Table.SelectRows

[markwieland]

I have following challenge:

Say I have a table like this:

Name	Type	Color	Size
A	Shirt	Red	XL
B	Shirt	Blue	XXL
C	Pants	Green	L

and a table like this:

Lookup filter name	Criteria
Shirts	(Type = “Shirt”)
Shirts XL	(Type = “Shirts” AND Size = “XL”)
Pants	(Type = “Pants”)

What I want to do is add data from the first table to the second table, like when you merge a query or use Table.SelectRows and then be able to expand the data to see which records from first table match the criteria of the lookup filter in the second.

I tried doing something like adding a custom column with “Table.SelectRows(LookupTable,Each[Criteria])” but received errors.

I think I need to write a function, but have no idea how to do that for this scenario?

Can someone help me?

[edhans · Accepted solution]

Oh good grief @ImkeF - duh!

Thanks! So obvious. I was looking for something difficlut.

@markwieland - the final formula for the filter is:

= Table.AddColumn(
            #"Changed Type",
            "Custom", 
            each
            let 
                varFilter = [Criteria]
            in
                Table.SelectRows(Table, Expression.Evaluate("each" & varFilter, [_ = _]))
        )

Here is the final link. I keep files in a different folder when they are solved. The above link will not work.

[markwieland · Accepted solution]

Wow @camargos88 @edhans @ImkeF  I usually never post questions on forums like this because I always thought it took days to get answers. You proved me wrong, less than 24 hours and I have some info to work with. I will try to apply your solutions to my actual case and let you know the outcome! Thanks!

[camargos88]

Hi @markwieland ,

I worked here, changing a bit your source to:

Lookup filter name CriteriaFilter

Shirts	[Type] = "Shirt"
Pants	[Type] = "Pants"
Shirts XL	[Type] = "Shirt" and [Size] = "XL"

I tried with the original but the brackets and lowercase logical operators were missing, maybe there is another way to do it.

And using the function that @edhans and @ImkeF provided:

Table.SelectRows(Table, Expression.Evaluate("each " & _filter, [_=_]))

[edhans · Accepted solution]

Oh good grief @ImkeF - duh!

Thanks! So obvious. I was looking for something difficlut.

@markwieland - the final formula for the filter is:

= Table.AddColumn(
            #"Changed Type",
            "Custom", 
            each
            let 
                varFilter = [Criteria]
            in
                Table.SelectRows(Table, Expression.Evaluate("each" & varFilter, [_ = _]))
        )

Here is the final link. I keep files in a different folder when they are solved. The above link will not work.

[markwieland · Accepted solution]

Wow @camargos88 @edhans @ImkeF  I usually never post questions on forums like this because I always thought it took days to get answers. You proved me wrong, less than 24 hours and I have some info to work with. I will try to apply your solutions to my actual case and let you know the outcome! Thanks!

[edhans · Accepted solution]

Glad to help @markwieland 

Remember, everyone here is a volunteer. Sometimes stuff can take days, other times minutes. Always post. Even if you discover a workable solution first, you can get a different perspective here that might improve on your solution.

[camargos88]

Hi @markwieland ,

Does your table Lookup have this the second column like this (Type = “Shirt”) ?

If yes, it's possible to query using table function, but more work.

If no, show us your base table and we can help you.

[markwieland]

Hi Ricardo, 

Thanks for replying!

I can't share the actual data, but the criteria column contains information like this:

((Type = "Shirt" AND Size ="XL") OR (Type = "Shirt") OR (Color = "Red")) AND Someother field = "X"

The complexity is that each row is different and uses different fields in a different order.

Can you help me with a function I can use?

[edhans]

I feel like I am close here, but this is returning an error:

= Table.AddColumn(
            #"Changed Type",
            "Custom", 
            each
            let 
                varFilter = [Criteria]
            in
                Table.SelectRows(Table, Expression.Evaluate(varFilter, [_ = _]))
        )

The problem is I am getting this error:

My PBIX file is here.

@ImkeF - can you see what I'm doing wrong here?

[ImkeF]

Hi @edhans ,

you're missing the "each" keyword that defines the function:

Table.SelectRows(Table, Expression.Evaluate("each" & varFilter, [_ = _]))