Merging causing prior remove duplicates to change what it removed?

[Ayrahvon]

I'm playing with a novel dataset (Baseball Projections) and I'm expanding all positional projections into a single file. This can create duplicates at the player level so I prioritized the most important positions first before expanding the tables out. Because of this when I remove duplicates, it removes the positions that are less important to fantasy baseball. The example I'll be using is Manny Machado who is eligible to play at SS and 3B. I want him to be SS only for the purpose of valuating what he's worth.

When I do the remove duplicates and as a step then filter to his name he's listed as SS. I ran all my numbers and then duplicated the query so that I could group by position and get average/standard deviation. I wanted those values to apply at the player level so I rejoined the duplicated query now grouped by position and the original player file. On merge - Manny Machado goes from SS to 3B. Somehow my two identical queries that are set to remove duplicates and keep the SS are instead keeping 3B? Since M is sequential I don't really understand how the merge is impacting a prior step of remove duplicates.

I've taken a screenshot to show you how I prioritized which positions expanded first to remove duplicates - that both queries show him as a SS prior to merge - and that after merge he shows at 3b.

Any advice on why or how M is behaving this way would be helpful - as I'm not understanding how it is getting to this result. I've attempted to change the JoinKind on the off chance the merge was being impacted by that, but to no avail.

I thought this was an interesting scenario I haven't encountered before so hopefully someone else has an idea of what it's doing!

Using unpatched version of excel 2016.