Order switching mid workflow

[helspaul]

Hi, I have the most frustrating issue. Anyone seen this before? Know how to stop it? Is it a bug?

I have a basic table which records changes over time

I want to add the date from row 2 to row 1 so I know the start and end date of the status 'NNC'

So I group my data by DefectID keeping All Rows, add an Index Column then expand. Each of my changes now has a sequential ID

Then I create a unqiue key and create another key for the record I want to join. My rows are in Date order, and my Index, Start and End columns are all in the right order

Then I merge using Start to End - everything is still in the right order

Then I expand

The expand is where this all goes wrong. The rows are in the same Date based order, but the Index, Start and End values have changed. The wrong row joins to the wrong row. 

When you flick between the steps you see the date order remain but the index, start and end change. And as a result the wrong end date is being added to the wrong row.

The data seems to revert back to the order it was read, but only part of it. I've tried breaking the process up into seperate tables with the same result. I've also tried adding sorting at various stages. I can make the Date order change and the Index, Start and End stay the same. But I can't make all the input values stay the same

Any ideas?

[v-pnaroju-msft]

Hi helspaul,

We are pleased to note that your issue has been resolved. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[Juan-Power-bi · Accepted solution]

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!

[helspaul]

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.