Forum Discussion

83dons's avatar
83dons
Helper III
1 year ago
Solved

Remove rows from one table from another larger table

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...
Need Help
  • Ashish_Excel's avatar
    Ashish_Excel
    1 year ago

    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.

     

83dons's avatar
83dons
Helper III
1 year ago

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

djurecic's avatar
djurecic
Super User
1 year ago

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's avatar
    83dons
    Helper III
    1 year ago

    Hi djurecic 

     

    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.