Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I cant figure out what is going on in the case below. Hope somebody can tell me what I might be doing wrong.
So I merged two tables based on the columns "UOKey" and "RepetitionNr". So far so good, when I check the individual rows, the join was succesfully performed, just as an example, see that row 1 matches up nicely (UOKey and RepNr). All other rows do too.
Now when I try to expand the rows, something strange happens. The columns in the row "EVENT_TIME", seem to shuffle for now clear reason. I assume that since I am only expanding, all rows of my UnitOperationStart (The starting table for the join), should remain tied together. But when I expand, see that the event times seem to shuffle, e.g. RepetitionNr 1, for UOKey "EMZ239D1BBRE521625LEGEN" first had event time 06:03:51, after expanding, it has 14:16:31 (see below) .
I would not expect this to happen. Why doesnt row 1 (or any row with repetition nr 1 and uokey "EMZ239D1BBRE521625LEGEN" still have EVENT_TIME 06:03 and the merged EVENT_TIME.1 of 06:39.38)?
Please help me, this is driving me bonkers! Thanks in advance
Here is my code from the advanced editor
let
Source = UnitOperationStart,
#"Removed Columns" = Table.RemoveColumns(Source,{"BatchID", "Unit", "UnitOperation", "Label"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"UOKey", "RepetitionNr"}, UnitOperationEnd, {"UOKey", "RepetitionNr"}, "UnitOperationEnd", JoinKind.LeftOuter),
#"Changed Type" = Table.TransformColumnTypes(#"Merged Queries",{{"EVENT_TIME", type datetime}}),
#"Expanded UnitOperationEnd" = Table.ExpandTableColumn(#"Changed Type", "UnitOperationEnd", {"EVENT_TIME", "RepetitionNr"}, {"EVENT_TIME.1", "RepetitionNr.1"})
in
#"Expanded UnitOperationEnd"
Solved! Go to Solution.
Hi @Anonymous ,
I just got an ideia...
How about if you Group, Sort and add the index column (those 2 last inside the [Data] column instead of sort / group / index ?
Like:
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"UnitOperationKey"}, {{"Data", each Table.AddIndexColumn(Table.Sort(_,{"StartTime"} ), "SequenceNr", 1,1), type table [UnitOperationKey=text, NoOfRepetitionsOfUO=number, StartTime=datetime, BatchID=text, Unit=text, Label=text, UnitOperation=text, SequenceNr=number]}})
Hi @Anonymous ,
I just got an ideia...
How about if you Group, Sort and add the index column (those 2 last inside the [Data] column instead of sort / group / index ?
Like:
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"UnitOperationKey"}, {{"Data", each Table.AddIndexColumn(Table.Sort(_,{"StartTime"} ), "SequenceNr", 1,1), type table [UnitOperationKey=text, NoOfRepetitionsOfUO=number, StartTime=datetime, BatchID=text, Unit=text, Label=text, UnitOperation=text, SequenceNr=number]}})
Thanks! This worked, but still I wonder why it goes wrong, seems like this is a workaround to a bug or somethign. Anyways, thanks for all the help!
Ok great. Will mask my data and upload it somewhere tomorrow. Thanks in advance for your help!
I know this post is almost two years old, but I wanted to report that I had the same issue.
If I filtered the data to get a smaller subset, I didn't have the issue of my original values getting mixed together (not just resorted, but mixed up), but with the full data set, it was a problem.
I resolved it by expanding and choosing to aggregate the field I wanted, and it worked. This definitely seems like a bug. I am using proprietary data so can't provide an example, but I wanted to report that this does appear to be a bug.
Is this on the development team's radar as a bug?
Hey man, just went through the same problem and found this topic too. In another communities I found people explaining that when sorting with large number of rows this sorting cannot be reliable for the next steps to take place.
In this topic I found that a Table.buffer with the sorting operation solves this issue:
https://social.technet.microsoft.com/Forums/en-US/545daa09-56b5-486a-b851-02cff46ea372/expanding-mer...
This has solved my issues. I really hope Microsoft fixes this or at least have a warning or a documentation about sorting operation that explains it.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.