Forum Discussion

dearwatson's avatar
dearwatson
Continued Contributor
6 years ago
Solved

Table Expand issue

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/EuL0GXNqNBVKnZFfwmgUTVkBjWiDWRF7BQxaegPLbnoiLg?e=4n4pg2 

 

 

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

 

 

 

 

 

Need Help
  • Fowmy's avatar
    Fowmy
    6 years ago

    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

1 Reply

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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