cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kannanAhammed
Helper IV
Helper IV

Remove rows which contain data in another Table

Hi all,

 

I have a data set like below

Fact table

IDContent
1a
2b
3c
4d
6e
7f
8g

 

another table 

 

ID
2
3
4
5
8

 

Output as below

 

IDContent
1a
6e
7f

 

that is I want to remove all the rows which is in the 2nd table to be removed from the first. I prefer the rows to be deleted from the first table itself rather than doing a merge. 

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

Hey @kannanAhammed ,

 

I'm wondering why you don't want to use a merge?

 

Using Power Query the left-anti join is designed for exactly this use case.

If the data will be refreshed, the Power BI dataset, will only contain the remaining rows.

 

The next screenshot shows the merge dialog. 

image.png

After the merge (Left Anti) my simple fact table will look like this:

image.png

Then, of course, you can remove the column that contains the table, and it's done.

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Jihwan_Kim
Super User
Super User

Picture1.png

 

New Table =
FILTER ( TableA, NOT ( TableA[ID] IN VALUES ( TableB[ID] ) ) )

 

 

Link to the sample pbix file 

 

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Picture1.png

 

New Table =
FILTER ( TableA, NOT ( TableA[ID] IN VALUES ( TableB[ID] ) ) )

 

 

Link to the sample pbix file 

 

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

TomMartens
Super User
Super User

Hey @kannanAhammed ,

 

I'm wondering why you don't want to use a merge?

 

Using Power Query the left-anti join is designed for exactly this use case.

If the data will be refreshed, the Power BI dataset, will only contain the remaining rows.

 

The next screenshot shows the merge dialog. 

image.png

After the merge (Left Anti) my simple fact table will look like this:

image.png

Then, of course, you can remove the column that contains the table, and it's done.

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

amitchandak
Super User
Super User

@kannanAhammed , do a left join in power query between 2 table and then delete all the rows which do have null value .

Or create this column and delete null rows in the merged table, assumed merged table has Table2.id

 

if [Table2.id] = null then null else 1

 

now delete null rows , option on right click

 

 

refer if needed

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!