Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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?

Need Help
  • edhans's avatar
    edhans
    6 years ago

    Oh good grief ImkeF - duh!

     

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

     

    Anonymous - 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.

  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
    edhans
    6 years ago

    Glad to help Anonymous 

     

    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.

8 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        edhans
        Community Champion
        6 years ago

        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?

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Anonymous ,

     

    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's avatar
      edhans
      Community Champion
      6 years ago

      Oh good grief ImkeF - duh!

       

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

       

      Anonymous - 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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!