Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIn 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.