Forum Discussion
Solved
Mark Differences Between 2 Table
Hi There, I have 2 tables, Table 1 and Table 2 and i am trying to mark items that unique/not in each other table We can find and list with EXCEPT but how I want to just to mark it down...
In that case, you can select the relevant columns first from both tables using "SELECTCOLUMNS" function
Then apply the above procedure
i.e.
Calculated Table = VAR firstTable = SELECTCOLUMNS ( Table1, "ID", [ID], "Serial", [Serial], "Name", [Name] ) VAR SecondTable = SELECTCOLUMNS ( Table2, "ID", [ID], "Serial", [Serial], "Name", [Name] ) VAR Common = INTERSECT ( firstTable, SecondTable ) VAR NotCommon = EXCEPT ( DISTINCT ( UNION ( firstTable, SecondTable ) ), Common ) RETURN UNION ( ADDCOLUMNS ( Common, "Unique", "No" ), ADDCOLUMNS ( NotCommon, "Unique", "Yes" ) )
Zubair_Muhammad , السلام عليكم
Yes you're right, John should be unique and marked as "Yes" too
Wa alaikumus salam
You can use this calculated table
From the Modelling Tab>>New TableCalculated Table = VAR Common = INTERSECT ( Table1, Table2 ) VAR NotCommon = EXCEPT ( DISTINCT ( UNION ( Table1, Table2 ) ), Common ) RETURN UNION ( ADDCOLUMNS ( Common, "Unique", "No" ), ADDCOLUMNS ( NotCommon, "Unique", "Yes" ) )Zubair_Muhammad I forgotten to mention those table have different structure and number of column . Wondering if Intersect can be based on certain column only, with name conversion
e.g. INTERSECT(TABLE1,"Serial Number",[Serial Number]),INTERSECT(TABLE2,"Serial Number",[serialNum1XXX)) ... ... ?
