I have a large table of devices and a second tiny table of search terms. I would like to filter the list of devices searching as a substring in the Device column or the Group column. I'm not sure if PQ can do Like Joins similar to SQL or if it is better to do this in DAX or M both of which I'm new to anyway. I've found a couple of online examples claiming to do something similar to what I want, but the M code was confusing and their descriptions on the solutions didn't make sense according to many others on the posts.

Can anyone give me some advice. I'm not sure what the performance will be like to do this in PQ. Executing a SQL query would be better, but isn't an option at present.

Thanks in advance.

E.g.: If the Search list is made up of ("server", "load", "uk") then I want to filter the Devices table on either the Device or Group columns looking for any row that contains them as case insensitive substrings, i.e. ("%server%", "%load%", "%uk%")

Search list (named range - not fixed number of entries, might be empty): -

Devices table (100k rows): -

Filtered Devices table (rows 6 and 7 removed): -

That's it. Hopefully simple.

CodePudding user response：

Yes, PQ supports joins.

This is another example for joining for your reference:

CodePudding user response：

If you do this in SQL you would do it the following way. You are not doing any join here. It is simple filtering.

Re: Performance - Depends on what you want to do? Is the is data-level or analysis-level? If later, then surely DAX. If prior and if you have a premium workspace take your data to datamart and you can apply fully qualified SQL there. In general, DAX has a much superior performance than PQ. PQ is not scalable with large data the same way SQL/DAX is.

select 
  * 
from 
  @t1 
where 
  c2 like '%server%' 
  or c2 like '%load%' 
  or c3 like '%server' 
  or c3 like '%load%'

The same can be replicated in a Dax Query

Table =
CALCULATETABLE (
    Devices,
    CONTAINSSTRING ( Devices[Device], "server" )
        || CONTAINSSTRING ( Devices[Device], "load" )
        || CONTAINSSTRING ( Devices[Device], "uk" )
        || CONTAINSSTRING ( Devices[Group], "server" )
        || CONTAINSSTRING ( Devices[Group], "load" )
        || CONTAINSSTRING ( Devices[Group], "uk" )
)

or better through a measure

maxRow =
CALCULATE (
    MAX ( Devices[Row] ),
    FILTER (
        Devices,
        CONTAINSSTRING ( Devices[Device], SELECTEDVALUE ( CAT[Column1] ) )
            || CONTAINSSTRING ( Devices[Group], SELECTEDVALUE ( CAT[Column1] ) )
    )
)