There is a quiet agony in watching Power Query return nulls for data you know is correct. The user did everything right: built a static list, verified it with XLOOKUP, checked the uniques, even manually eyeballed samples. And still, 70 percent of the merges come back blank. The frustration is not a failure of effort; it is a failure of assumption. The assumption is that when two values look identical, the software sees them as identical too.
Power Query does not care about how a name appears to your eye. It cares about what is actually in the cell: trailing spaces, non-breaking spaces, different Unicode characters that render identically, line breaks hiding at the end of a string, or even a subtle difference in case sensitivity depending on your locale and the join kind you selected. The user said the names are written in the exact same way in the static list. But "exact" is a human judgment. The machine is literal to a fault. When you merge on a column, Power Query performs an exact match on the underlying binary values. A space that your text editor does not display is still a space. A soft hyphen that looks like a dash is not a dash. The 30 percent that matched are the values where the bytes aligned. The other 70 percent are not wrong; they are just different at a level you cannot see without inspecting the character codes.
The practical takeaway here is not to abandon the static list approach. It is to stop treating the merge as a single step and start treating it as a diagnostic process. Use the Table.TransformColumns with Text.Trim to remove leading and trailing spaces, then apply Text.Clean to strip out non-printing characters. Better yet, use the "Replace Values" with a non-breaking space character (character code 160) and replace it with a regular space. Then re-run the merge. If you still see nulls, add a custom column that compares the ASCII codes of each character in both columns. That will tell you exactly where the divergence begins. The user already did the hard part: they built a reliable reference list. The remaining work is not about data quality; it is about data normalization. And that is a solvable problem, not a mystery.
The deeper lesson is that fuzzy matching is not the enemy, and neither is exact matching. The enemy is assuming that "clean" is a visual property. Clean data is a structural property. It means every cell in a given column follows the same rules for spacing, case, and character encoding. The user's manual checks were a good start, but they were checks of the eyes, not the bytes. So here is the concrete next step: before merging, run a transformation that trims, cleans, and optionally lowercases both the source and the static list. Then merge again. If nulls persist, add a column that shows the length of each value and the count of spaces. That will expose the invisible. The solution is not to give up on the static list; it is to make the static list as literal as the machine that reads it.