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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.