This story from a Power Query user lands like a cold splash of reality. The mistake is simple: a "Remove Duplicates" step that did not respect the sort order the user had already applied. In Excel, the operation behaves intuitively, keep the first occurrence in a sorted list. In Power Query, it keeps the first occurrence in the original table order, regardless of any prior sorting. The result was a dataset that looked correct but contained the wrong password for Bob. One data point. One error that someone else caught. And a lingering fear that other non-obvious traps are waiting.
We think this failure reveals something deeper than a quirk in Power Query. It exposes a hidden risk that exists in every tool that promises to automate data work: the gap between what a feature *should* do and what it *actually* does. The user here is an experienced operator. They knew enough to sort, deduplicate, and expect a specific outcome. But the tool's internal logic diverged from the mental model that years of Excel had built. That divergence is the risk. It is not documented in bold letters. It does not trigger a warning. It simply produces wrong data, silently, and waits for someone to notice.
The practical takeaway for anyone managing data is not to abandon Power Query or any other tool. It is to recognize that every transformation step carries assumptions about order, uniqueness, and data types that may not match your expectations. The workaround the user discovered, grouping by name, calculating the max date, then merging, is a legitimate solution. But it is also a symptom. The real fix is to build a habit of verifying each step's output against a small, known dataset before trusting it at scale. Test the edge cases. Confirm that the tool's behavior matches your intent, not just your memory of how another tool worked.
This is not a critique of Power Query specifically. Every spreadsheet tool, every database, every script has these asymmetries. The hidden risk is the assumption that what worked in one environment will behave identically in another. The user's concluding question, "What else do I need to look out for?", is the right one. The answer is not a list of gotchas. It is a discipline: treat every automated step as a hypothesis, and validate it before you rely on it. That is the only way to keep one bad data point from becoming the one that matters.