cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pedro_Israel1
Regular Visitor

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

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

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

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

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.