Compare Values in Master Table against several other tables

[BarryFletcher]

Hi all,

I have a table which has a column of equipment and each piece of equipment has a unieue tag number. I also have other tables which "should" contain all the values from within the master table. Is there an easy way I can get a list of the tag numbers that appear on the MasterTable that do not appear in the other tables?

So I have MasterTable with 100 rows, within the other 5 tables theses sould all add up to 100 rows with different vlaues in each.

Can I compare table1, table2, table 3 to the MasterTable and list the missing rows?

[Anonymous · Accepted solution]

Hi,

Thanks for the solution 3CloudThomas offered, and i want to offer some more infotmation for user to refer to.

hello @BarryFletcher , you can refer to the following sample.

Sample data 

master

Table 1

Table 2

Create a measure

Missing_id =
VAR a =
    EXCEPT ( ALLSELECTED ( 'Table 1'[id] ), ALLSELECTED ( master[id] ) )
VAR b =
    EXCEPT ( ALLSELECTED ( 'Table 2'[id] ), ALLSELECTED ( master[id] ) )
VAR c =
    SUMMARIZE ( UNION ( a, b ), [id] )
RETURN
    CONCATENATEX ( c, [id], "," )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Merge the 5 tables together in PowerQuery (only the equipment Id to match master table). Then, get a distinct list of equipment Ids. Join to the Master table with a left anti joni and this will be a list of master table equipment id that does not exist in the other tables