Forum Discussion
Helper IIIRemove 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 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
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 SourceHope this helps.
7 Replies
Ashish_ExcelSolution Supplier
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 SourceHope this helps.
- djurecic
Super User
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
- djurecic
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
83donsHi 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.
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 djurecic . 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.
