Remove rows which contain data in another Table

[Anonymous]

Hi all,

I have a data set like below

Fact table

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

another table 

ID

2

3

4

5

8

Output as below

ID	Content
1	a
6	e
7	f

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. 

[TomMartens · Accepted solution]

Hey @Anonymous ,

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. 

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

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

[Jihwan_Kim · Accepted solution]

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

Link to the sample pbix file 

[TomMartens · Accepted solution]

Hey @Anonymous ,

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. 

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

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

[amitchandak]

@Anonymous , 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