Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Expanding column after left join shuffles values in single column

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]}})

     

     

     

27 Replies

  • Looks like your composite key is not properly describing the relationship between the two tables.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Ibendin, thanks for your response. How do I fix this? 

      • lbendlin's avatar
        lbendlin
        Super User

        Include the event time in the composite key or use index columns.

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support

    Hi Anonymous ,

     

    Try to change the order of "Changed Type" and "Expanded UnitOperationEnd".

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      I have removed the change type step, but I dont see how this changes anything. Also swapping the two steps doesnt change anything.