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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors