Compare values in 2 tables sets and display the difference

[Matt_Longworth]

Hi guys.

I am doing a excel master sheet which has all the Users names, emails, AD info in it and also has their Device info.

I have managed to collect all the info from power query but now am struggling on how to report this all.

Im wanting to create a table which has differences in it like the AD has a name but the master sheet hasnt been updated with this info.

I have the relationship going for it so I can click a name on the master sheet and it shows on the table beside it but I know there are names missing because I have purposefully left them out.

I tried to figure it out by using ths post https://community.powerbi.com/t5/Desktop/Compare-two-tables-and-display-missing-rows-from-the-first-... but its not doing what I needed it to do.

I also want to check if a user is disabled in the AD (514) but hasnt been updated in the Master sheet as we need to track down equipment.

I know that there are probably better ways to do this but the IT director wants an excel so im trying to get it so we can check both places and see if the New starter/leaver process is working as it should be.

Any ideas or tips is greatly appreciated.

Thanks

Matt

Test Power BI file 

[Matt_Longworth]

Thanks. Sorry I have been flat out at work this week and only just got a few mins away to look at this in more detail.

I was just not understanding it until for some reason a lightbulb popped in and it all made sense.

Its showing the values that I would sort of expect but for some reason its not showing them correctly but the solution you had does makes sense, i think its just my data not mapped right.

Thanks

Matt

[Anonymous · Accepted solution]

Hi @Matt_Longworth ,

I can't open the pbix link you provided.

You can insert tables directly into the reply, providing dummy data and expected results. 

For the post you provided, pay attention to the table in CONTAINS, if you want to display table (1), you need to put table (2) in the formula.

Measure = CONTAINS('Table (2)','Table (2)'[Name],MAX('Table'[Name]))*1

Best Regards,

Stephen Tao

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