Power Query help to extract unique values

[Kailef]

So in this case, I am working with a roster list that changes weekly. I have posted a screenshot that shows a small example of what i am dealing with. We're talking literally 1000's of agents that I need to manage and report weekly on.

So we have 4 columns set up. I have the Power Query set up to retrieve the live roster that gets updated weekly. I haven't set it up yet, but I plan to set up a power query to pull the "Our Roster" that we're dealing with that week. I also have 2 other columns, which will display the unique names that are not found in the other column.

I need to compare the "Agent Live" and "Our Roster" columns. I need to extract the unique ones. So in this case, for example, "Aaron Sloan" will not be moved over to one of the two other columns. I need to make sure "Adrian Milian" gets put in the "New Active Agents" column where as the "Aaron Pomraning" appears in the "No Longer Active Agents" column.

Is this possible? I know by just going into the sheet and not powerquery, I can goto conditional formatting and instead of highlighting duplicates, I can set it to "unique" and highlight those, but I need to pull the unique ones into either a single column, or if possible and preferably, the new new columns I have. If Power Query isn't an option, I'm open to other ideas that would solve my problem. I thought about doing a VLOOKUP, but I wanted to avoid blank cells in between names and having to scroll way down.

[Kailef]

I just wanted to follow up on this once more. Your solution worked great to pull unique data from 2 different columns when compared against each other. I was wondering if it's possible to pull unique data when comparing 2 columns against 2 other columns. So for example, I have this data where the date and ID is listed. One column contains the ID and the date it was submitted on the server where the submission went through, but then we have a tracking report that gets updated to the minute based on date and ID that's put in by the user. So the tracking sheet may contain inaccurate information compared to what's on the server.

Is there a way to pull out the unique ones if the ID and the date that goes with it is not found in the other 2 columns?

For example, I need it to list the date and ID from Column A & B if those 2 are not found in Column C and D. The ID may match, but the dates with them are different, so those need to be pulled. There is maybe multiple audits with the same date, but as long as that ID and the date that goes with it are in both A/B and C/D, then it's not pulled into the Unique Dates/IDs.

Basically, if the ID is in B and not in D, it pulls it the date that goes with it. If the ID is in D and not B, it pulls the date that goes with the ID in D. Hopefully that makes sense.

How different would the steps by to set this up, if it's even possible?

[Kailef]

So maybe I'm doing this wrong. This is actually within Excel and using their Power Query. Maybe I shouldn't have posted this here, but since it is here, maybe you or someone else can still help.

I get this error when I try to follow your steps in Excel.

[Ahmedx]

I wrote that you should change the name of the last step to unique

[Kailef]

I did not even see that first part where I need to add the step and rename the step. That did it. I apologize. It's late and I've been at this for hours. My eyes are tied and I'm exhausted from a long 12 hour shift.

You just saved me a crapload of work. Thank you so much.

I was using a IFERROR Index kind of formula for 1000's of agents and it severely slowed the excel sheet. The Power query is so much faster and easier. I was contemplating removing the formula to make it run smoother and and manually pick them out.

I need to add this to another workbook also so I can pull out unique ID's to determine what ID's were completed and which ones were not when comparing to a sheet that contains the data of all potential completed ID's.

Thanks again!

[Ahmedx · Accepted solution]

Sample PBIX file attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhjELX0063Mh6apM9?e=cP00ai