Forum Discussion

Tcoe12's avatar
Tcoe12
New Member
8 years ago
Solved

Merge returning non existent values

Important previous steps there is a 'Custom group by' that produces an index column with 1 being the latest leave date, 2 being second latest etc.  The data has also been filtered to just 1 Personnel...
Data Modeling
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    The cause of your issue is a known phenomenon: if you sort a table and then use Table.GroupBy or Table.Distinct with the sorted table as source, the sort order is not necessarily maintained.

    In your case, it means that the nested tables can have a different sort order then the original table.

    Apparently you can even have different results in different queries.

     

    The solution is to buffer the sorted table:

     

        #"Sorted Rows" = Table.Buffer(Table.Sort(#"Replaced Values1",{{"HcmWorker_FK_PersonnelNumber", Order.Ascending},{"StartDateLeave", Order.Descending},{"EndDate", Order.Descending}})),

     

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

The cause of your issue is a known phenomenon: if you sort a table and then use Table.GroupBy or Table.Distinct with the sorted table as source, the sort order is not necessarily maintained.

In your case, it means that the nested tables can have a different sort order then the original table.

Apparently you can even have different results in different queries.

 

The solution is to buffer the sorted table:

 

    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Replaced Values1",{{"HcmWorker_FK_PersonnelNumber", Order.Ascending},{"StartDateLeave", Order.Descending},{"EndDate", Order.Descending}})),

 

Tcoe's avatar
Tcoe
Frequent Visitor
8 years ago

I really appreciate(you both) you taking your time to help me with this issue, in particular the work around that you have provided.

 

Hopefully MS can straighten out this 'known phenomenon',

 

Thanks again

 

Tcoe