- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Delete rows with "vlookup" = Left Anti Join
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

Helpful resources
User | Count |
---|---|
137 | |
121 | |
82 | |
62 | |
46 |