Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

August Carousel

Fabric Community Update - August 2024

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