SQL NOT LIKE in M language

[mdor]

Hello,

I have an SQL query that contains the NOT LIKE function. I have googled for an hour and didn't find a good solution. Can anyone help me with this task? Here is my SQL query:

SELECT
  *
FROM Product
Where  Description NOT LIKE '' and Product NOT LIKE 'CMI' 

Thanks in advance 

[edhans · Accepted solution]

In Power Query, it would be:

Table.SelectRows(TableName, each not Text.StartsWith([Description], "") and not Text.StartsWith([Product], "CMI"))

Power Query doesn't have a LIKE operator. You'd use Text.Contains, Text.StartsWith, or Text.EndsWith depending on what you are trying to do. It does have a NOT operator, which is simply not - all lower case. 

[PhilipTreacy]

Hi @mdor 

I'm not sure what your question is?  That is a valid SQL statement although you may be looking to amend the the 2nd part to 

and Product NOT LIKE '%CMI%' 

That said, if you write your own query like this you will break query folding and PQ will do all the hard work instead of the SQL database.

https://docs.microsoft.com/en-us/power-query/power-query-folding

You would be better off loading the table into PQ then filtering out the data you want within PQ and then letting it tell the SQL database what data to send.

Phil