Please see attached file, in particular, Bud Men Roster Query. Starting from top, everything is fine until the expansion of the "ADP with Drafted." I have no idea why, but it's changing the "Distinct Pos" of Carlos Santana and Paul DeJong after the expansion. Santana goes from C/IF1 to C/IF2 and DeJong goes from C/IF2 to C/IF1. What's causing them to change? The same occurs with SP Zach Wheeler and SP James Paxton. Wheeler goes from SP2 to SP3 and Paxton goes from SP3 to SP2. I've redone this several times, multiple hours. I cannot figure out what's causing this. The merged numbers match up perfectly. Any suggestions? It's driving me nuts.
let
Source = Draft_Results_Reference,
#"Filtered Rows" = Table.SelectRows(Source, each ([Team] = "Bud Men") and ([Player] <> null)),
#"Merged Queries1" = Table.NestedJoin(#"Filtered Rows", {"NFBCID"}, #"ADP with Drafted", {"ID"}, "ADP", JoinKind.LeftOuter),
#"Expanded ADP with Drafted" = Table.ExpandTableColumn(#"Merged Queries1", "ADP", {"C", "1B", "2B", "3B", "SS", "OF", "RP", "SP"}, {"C", "1B", "2B", "3B", "SS", "OF", "RP", "SP"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded ADP with Drafted", {"Distinct Pos"}, Positions, {"Distinct Pos"}, "Positions", JoinKind.RightOuter),
#"Expanded Positions" = Table.ExpandTableColumn(#"Merged Queries", "Positions", {"Pos", "Distinct Pos", "Order"}, {"Pos.1", "Distinct Pos.1", "Order"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Positions",{"Rd", "Pick", "Player", "$", "FAAB", "C", "1B", "2B", "3B", "SS", "OF", "RP", "SP", "Pos.1", "Order"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Order", "Pos.1", "Player", "Rd", "Pick", "$", "FAAB", "C", "1B", "2B", "3B", "SS", "OF", "SP", "RP"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Pos.1", "Pos"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","",null,Replacer.ReplaceValue,{"C", "1B", "2B", "3B", "SS", "OF", "SP", "RP"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"C", type any}, {"1B", type any}, {"2B", type any}, {"3B", type any}, {"SS", type any}, {"OF", type any}, {"SP", type any}, {"RP", type any}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Order", Order.Ascending}})
in
#"Sorted Rows"
Still not smart enough to figure out my mistake. However, all that I did was added an index column prior to the #"Expanded ADP with Drafted" and that solved the problem.