Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dearwatson
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
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

1 REPLY 1
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.