Remove rows from one table from another larger table

[83dons]

Hi I have a Master table and a Duplictaes Table. I require to remove all rows from the Master Table that are in the Duplicate table. The columns are identical in both tables. Both tables have StaffID as the main identifier and each staff member can have multiple roles identified by RoleID. The Duplicate table shows staff members who have more than one role with the same RoleID. Some of the other fields may differ in the other columns to make the rows slightly different in the duplicates. I require a way of removing all rows listed in the Duplicate table from the Master table.

eg one tables rows are matched and removed from the bigger table. I would like the query to specifically look up the tows from the duolicate table in the master table then remove them if it finds a match. I dont want a rule applied to the master table without reference to the duplicate table. Thanks.

Duplicate Table

StaffID   RoleID

0001      R00123

0001      R00123

0004      R00762

0004      R00762

0007      R00932

0007      R00932

0007      R00932

0010      R00986

0010      R00986

Master Table

StaffID   RoleID

0001      R00123

0001      R00123

0002      R00821

0003      R00142

0004      R00762

0004      R00762

0005      R00555

0005      R00100

0006      R00418

0006      R00888

0006      R00034

0007      R00932

0007      R00932

0007      R00932

0008      R00067

0009      R00028

0010      R00986

0010      R00986

Master Table after query is run:

StaffID   RoleID

0002      R00821

0003      R00142

0005      R00555

0005      R00100

0006      R00418

0006      R00888

0006      R00034

0008      R00067

0009      R00028

[v-tsaipranay]

Hi @83dons ,

Thank you for reaching out to Microsoft fabric community forum.

I wanted to check if you had the opportunity to review the information provided by @Ashish_Excel and @djurecicK2 . Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Ashish_Excel · Accepted solution]

Hi,

in each table remove the extra space from both headings.  This simple M code in Power Query works

let
    Source = Table.RemoveMatchingRows(master,Table.ToRecords(duplicate),{"StaffID","RoleID"})
in
    Source

 Hope this helps.

[djurecicK2]

Hi @83dons ,

 Ok, you can also do this in Power Query with a Left Anti Join:

https://community.fabric.microsoft.com/t5/Desktop/Remove-rows-which-contain-data-in-another-Table/m-...

[83dons]

@djurecicK2 I dont really know how to use DAX can you provide a little more detail please or an alternative that doesnt use DAX?

[djurecicK2]

Here is an example with Table 1 and Table 2 where you want to filter out rows that are in Table 2

Table 1	Table 2

From Power BI Desktop, create a new table using the following DAX:

NewTable = CALCULATETABLE(EXCEPT(Table1, Table2))

Which gives you A | 2 since it is the only row in Table 1 that is not in Table 2

[83dons]

Hi @djurecicK2 

I got this to create the new table in the Model View but I cant see table listed when I click Transform Data and that window opens. I usually use thre Query list down the left hand side to go between my tables/queries and the new one isnt listed. Is there something I need to do to be able to see that table to transform? Do I need to try the other merthod with no DAX in order to see this table in the transform page?

There is a symbol on the new table not on the others:

Table doesnt list on the Transform page, I need to go on and apply various trasnform steps now:

I have settled for the other solution as it keeps the table in the transform window. Thanks.

[djurecicK2]

Hi @83dons ,

 If you want to use DAX for this, check out the EXCEPT function:

https://www.youtube.com/watch?v=Kk7_TdmxQOI

https://learn.microsoft.com/en-us/dax/except-function-dax