There's a quiet assumption buried in most Power Query workflows: that if the logic looks right, the data will follow. This reader's experience with appending three workbooks is a sharp reminder that the order of operations matters more than the intent behind them. When the third workbook's new rows started masquerading as duplicates from the second, the root cause wasn't a data problem. It was a key problem. The index column, so often treated as a neutral anchor, was actually a fragile crutch.
Here's what happened in plain terms. The query combined tables, then used `Table.Distinct` on the index column to remove duplicates. That works beautifully when each row has a unique, stable identifier. But when you append three sources and rely on a positional index, you're betting that the order of rows will stay consistent across refreshes. It won't. The third workbook's data didn't break because it was new. It broke because its index values collided with the second workbook's, and Power Query decided the first match wins. Flipping the append order masked the symptom by changing which rows got discarded, but it introduced another problem: the manual comments disappeared because they were tied to a row's position, not its identity.
The fix the reader is already circling is the right one. You need a composite key, something like `Index & WorkbookName`, to make each row globally unique. That means carrying the source filename through the transform, not stripping it out early in the pipeline. It's an extra step, but it's the difference between a query that survives contact with real-world data and one that quietly corrupts itself. The manual comments are valuable precisely because they're manual. They represent human judgment layered on top of automated processes. Losing them because of a lazy key is not a technical failure. It's a design failure.
So here's the takeaway, and it's not about fixing this one query. It's about interrogating every assumption you've baked into your data model. If your key is an index, ask yourself: does this key mean anything outside this exact moment in time? If the answer is no, you're building on sand. Power Query is powerful, but it will happily amplify a flawed foundation into a consistent, reproducible error. The solution isn't to append faster or reorder tables. It's to give every row an identity that survives contact with other data. Do that, and your comments, your context, and your confidence will stick around too.