Delete rows with "vlookup" = Left Anti Join

[Anonymous]

I have a table with sales = "avocado", in the column= "Region" I have cities but also states. I want to drop all sales with a state in column."region". I did it on Excel but I want to learn the process in powerBI desktop. 

My idea was the following:

I have a table with state names (they are well written in both tables).

So i would add a column on avocado.table making a vlookup on states.table invoking the 2nd column with value "1". 

With this new column with a 1 for each state sale. I would just filter that column unchecking the "1" and all remaining rows would be citie's sales. 

Couldnn´t find the way, any help?

Here is the image for better understanding:

a

[HotChilli · Accepted solution]

Use the Merge Queries function (Merge as New is probably better for experimenting).  If you use a Left Anti Join that should get you what you want but it may not be obvious what it is doing. 

You could try a Left Join to return the state from 'usa_states' and then filter out the 'null' states. (if that makes more sense)

Let me know how you get on

[Anonymous]

Thank You so much, "Left Anti Join" is the function I was looking for. 

Its output was easy to understand= it keeps non-duplicated rows on the ID_column from the first table.