cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Responsive Resident
Responsive Resident

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:

dearwatson_0-1595567715163.png

 

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

dearwatson_1-1595567851783.png

 

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.

dearwatson_2-1595568012906.png

 

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

 

 

 

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

@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

Fowmy_0-1595577139686.png

Same after ExpandFowmy_1-1595577155388.png

________________________

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

I accept KUDOS 🙂

YouTube, LinkedIn

View solution in original post

1 REPLY 1
Highlighted
Community Champion
Community Champion

@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

Fowmy_0-1595577139686.png

Same after ExpandFowmy_1-1595577155388.png

________________________

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

I accept KUDOS 🙂

YouTube, LinkedIn

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors