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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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