Forum Discussion
Order switching mid workflow
Hey, this is a really common gotcha in Power Query — row order is never truly guaranteed between steps, so when you expand after the merge, the engine can internally reorder things and your Index no longer lines up correctly.
The safest fix is to sort explicitly by DefectID + Date right before you add the Index column, and then make sure your key is built on both those columns together, not just the index. Here's what I'd recommend:Before adding the Index, add a sort step: sort by DefectID ascending, then Date ascending. You can do this in the UI or in M like this:
m= Table.Sort(#"Previous Step", {{"DefectID", Order.Ascending}, {"Date", Order.Ascending}})
Then group by DefectID keeping All Rows, and inside each group add the index — this way the index is scoped per DefectID and the order within each group is controlled.
When you build your join keys, make sure they combine DefectID + Index so the match is always within the same defect.The root cause is that Power Query's query engine doesn't treat intermediate sort steps as "sticky" — it's lazy-evaluated and can re-execute steps in a different physical order. Sorting immediately before the index generation is the only reliable way to guarantee they stay in sync.
Hope that helps!
Hey, this is a really common gotcha in Power Query — row order is never truly guaranteed between steps, so when you expand after the merge, the engine can internally reorder things and your Index no longer lines up correctly.
The safest fix is to sort explicitly by DefectID + Date right before you add the Index column, and then make sure your key is built on both those columns together, not just the index. Here's what I'd recommend:
Before adding the Index, add a sort step: sort by DefectID ascending, then Date ascending. You can do this in the UI or in M like this:
m= Table.Sort(#"Previous Step", {{"DefectID", Order.Ascending}, {"Date", Order.Ascending}})
Then group by DefectID keeping All Rows, and inside each group add the index — this way the index is scoped per DefectID and the order within each group is controlled.
When you build your join keys, make sure they combine DefectID + Index so the match is always within the same defect.
The root cause is that Power Query's query engine doesn't treat intermediate sort steps as "sticky" — it's lazy-evaluated and can re-execute steps in a different physical order. Sorting immediately before the index generation is the only reliable way to guarantee they stay in sync.
Hope that helps!
Thanks for this. I tested it out and it did work. But just to be sure I also spoke to the team who make the API. They added the ability to sort on the date/time column. Now my data is being read in the order I need it from the start so Power BI isn't sorting it, and can't unsort it part way through.
