Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

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:

 

aa

 

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

Syndicated - Outbound

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

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

Syndicated - Outbound

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

avatar user
Anonymous
Not applicable

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. 

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)