Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |