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"
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]}})
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Hi @Anonymous ,
Is it possible to take a look at your pbix file ?
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
@Anonymous ,
You can use google drive/one drive/dropbox and share the link here or through private message.
Please mask any sensitive information.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Hi @Anonymous ,
Have you tried it with those rows you found the issue ?
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
@Anonymous ,
If I got it right, can you make a csv only with those values with weird behaviour ?
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
@Anonymous ,
Can you provide the m code for both tables ?
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
@Anonymous ,
Try resuming those rename/remove/reorder steps in less steps, if possible one for each.
Also, can you group and create this index column after the merge ? (You don't need the sort step in order to group).
Let's try eliminating some steps to check if the merge step is impacted with the previous.
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
@Anonymous ,
I've seem this situation couple times. It looks like a problem with query evaluation.
We can try something like:
0)
Delete your data cache: Options -> Data Load -> Clear Cache.
Use different name for the columns like:
Table UOEnd -> UnitOperationKey to UnitOperationKey_End
1)
Use the Table.Buffer function, your query may run slower.
2)
Delete the #"Expanded UOEnd" and #"Merged Queries" steps, create a custom column with this code:
let _UnitOperationKey = [UnitOperationKey], _SequenceNr = [SequenceNr] in
Table.SelectRows(UOEnd, each [UnitOperationKey] = _UnitOperationKey and [SequenceNr] = _SequenceNr)
and expand this column.
3)
Try creating a column with UnitOperationKey and SequenceNr, and join it using only this new column.
4)
Your source is SQL, so we can try pushing the merge to the database through Folding Query (just need to adapt some steps for it).
https://docs.microsoft.com/en-us/power-query/power-query-folding
5)
Write this query as a custom SQL, so you just bring the desired result.
I would try the item 2 and 3 first. Btw what is the size of your dataset ?
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
