Forum Discussion
Remove rows which contain data in another Table
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.
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
New Table =
FILTER ( TableA, NOT ( TableA[ID] IN VALUES ( TableB[ID] ) ) )
3 Replies
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
New Table =
FILTER ( TableA, NOT ( TableA[ID] IN VALUES ( TableB[ID] ) ) )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
