Table Expand issue

[dearwatson]

Hi Folks,

Having a wierd issue I hope someone can explain, trying to perform a "self join" in Power Query with a duplicate table to find the next event in a series of events. 

Data looks like this:

I can merge the duplicate table ok and the table preview shows the correct data like so:

Note that the next event for this object after "Doctor Seen" is "Bed Request"

The issue is that when I click Expand on the column the data swaps around! In this example when I click expand Doctor Seen moves to Index postion 6 and Departure Time moves to position 2 and joins to Bed Request.

I have tried:

1. Self Joining to the table itself.. same issue
2. comepletely rebuilding the tables from scratch with new spreadsheet... same issue
3. copying the data into embedded (Enter Data) tables and the join -> expand.. this works correctly?? 

So I think its a BUG... any ideas?

Link to PBIX and sample data: https://metroholographics-my.sharepoint.com/:f:/g/personal/admin_dearwatson_net_au/EuL0GXNqNBVKnZFfw... 

To replicate go to the "Events" table in Power Query and observe the "Merged Queries" step then the "Expanded DuplicateEvents" Step

[Fowmy · Accepted solution]

@dearwatson 

I checked and found the culprit; it seems to be the sort that doesn't retain the status, joins change order. Wrap your

Sorted Rows  step in  Events Table as 

Table.Buffer(Table.Sort(#"Renamed Columns",{{"atd_id", Order.Descending}, {"Value", Order.Ascending}}))

Before Expand

Same after Expand

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn