I am trying to compare two tables so I can isolate the items that do not appear on both tables. I have tried this
Table.AddColumn(#“Changed Type”,“InDates and InEmp”, each List.AllTrue({List.Contains(ListTkMasterEndDates,[EndDates]),List.Contains(ListtkEmployee,[Employee])})) to create a True or False but it does not seem to give me the results I want.
I have two table tkMasterEndDates and another table EMALLCompanyEndDates
I created 2 list of dates and employee from the tkMasterEndDates table and used the formula above to add a custom column to EMALLCompanyEndDates to give me a TRUE or FALSE but it is not given me the correct answer. So for example employee 127040 should be FALSE for the end dates 22/08/2021 00:00,29/08/2021 00:00, 05/09/2021 00:00 in EMALLCompanyEndDates but for some reason it gives me a TRUE
Give this a go instead, using Table.RemoveMatchingRows to identify the difference.
Note that record field names need to match for comparison so change either EndDate / EndDates
@Melissa thanks for this it is definitely what I was looking for but rather than just for employee 127040 I want a list of all employees that don’t match. When I try this I get a message below
@Melissa What was the reason why this Table.AddColumn(#“Changed Type”,“InDates and InEmp”, each List.AllTrue({List.Contains(ListTkMasterEndDates,[EndDates]),List.Contains(ListtkEmployee,[Employee])})) didn’t work? I took it as a solution you gave me for another issued I had and it work then not sure why it didn’t work now?
But you have been great and helpful and I am really grateful.
Your query was different then, you only needed to know if a set of values was present in related tables but not if the full record matched with a record in another table - that’s what we’ve done here.
Don’t know if you’ve subscribed to the eDNA YouTube channel but the video covering this topic was released today. I’ll leave the link for you here. Hope you enjoy it.
@Melissa lol. I clocked it when it appeared on my YouTube feed. I was like “I think I recognize those end dates”. But all good if it helps others I’m all for it. Do you have to do a video every week?
No certainly not, but I do take requests… LOL
When a topic comes through this (public) forum, I feel could benefit others, I make a note of that so I can do a video on it when I have the time.
