cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tcoe12 New Member
New Member

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 Number in this example, this problem occurs regardless of how many or what Personnel Number

 

In this table 'Emp leaves latest' you can see one row of information, the Filtered Rows1 step is where Index column created by a 'Custom Group by' has been filtered to = 1. As you can see below the only Leave Type remaining here is "layoff'. I am looking to Merge this table in to another. 'Injury/Illness' was another Leave Type but was filtered out as the Index in that row = 2

 

BUG.PNG

In the picture below you are able to see that this table 'Workers' has only one row, I have made sure that the Personnel Number is the same in both tables of course.

Please see the merge below. You are able to see in the preview that the only Leave Type available is 'Layoff'

BUG MERGE.PNG

The Picture below shows us the step once the newly merged table has been expanded, you can see that the Leave Type that has come through is 'Injury/ Illness'. This shouldn't be 

BUG Final.PNG

 

 

 

 

Any ideas or help would be appreicted.

 

Trystan

1 ACCEPTED SOLUTION

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Merge returning non existent values

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

 

Specializing in Power Query Formula Language (M)
7 REPLIES 7
Highlighted
CheenuSing Super Contributor
Super Contributor

Re: Merge returning non existent values

Hi @Tcoe12

 

Is it possible to share the raw data of employees leave latest in excel format and share the link here for finding  a solution.

 

Also can you paste the query using Advanced editor for the employees leaves latest step, to check if there is any thing is missing.

 

Cheers

 

CheenuSing 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Tcoe Frequent Visitor
Frequent Visitor

Re: Merge returning non existent values

Hi CheenuSing,

 

Many thanks for your response and offer of assistance.

 

I have attached the Excel file, and have pasted the Advanced Editor below for the whole of Employees Leave Latest

Tcoe Frequent Visitor
Frequent Visitor

Re: Merge returning non existent values

Hi CheenuSing,

 

Many thanks for your response and offer of assistance.

 

Here is the link to the Excel data type  https://1drv.ms/x/s!Aq-6rFtHe6YQaBvE3ECQ3h2nZ2Q 

 

Here is the Advanced Editor below for the whole of Employees Leaves Latest (this advanced editor is working with Desktop excel workbook) and below the Employee Leaves Latest is the code for the 'Workers' table

  

 

Employee Leaves Latest

let
    Source = Excel.Workbook(File.Contents("C:\Users\tcoe\Documents\Emp Masters Issue.xlsx"), null, true),
    #"Emp Leaves Latest_Sheet" = Source{[Item="Emp Leaves Latest",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Emp Leaves Latest_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"HcmLeaveType_FK_LeaveTypeID", type text}, {"HcmWorker_FK_PersonnelNumber", type text}, {"Note", type text}, {"CompanyInfo_FK_DataArea", Int64.Type}, {"HcmEmployment_FK_ValidFrom", type datetime}, {"StartDateLeave", type datetime}, {"CompanyInfo_FK_PartyNumber", Int64.Type}, {"EndDate", type datetime}, {"HcmEmployment_FK_ValidTo", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"EndDate", type date}}),
 #"Added Custom" = Table.AddColumn(#"Changed Type1", "Leave Active YN", each if ([EndDate] > DateTime.Date(DateTime.LocalNow())) then ("Y") else ("N")),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Leave Active YN] = "Y")),
    #"Replaced Values" = Table.ReplaceValue(#"Filtered Rows",#date(2154, 12, 31),null,Replacer.ReplaceValue,{"EndDate"}),
    #"Replaced Values1" = Table.ReplaceValue(#"Replaced Values",#date(2063, 12, 31),null,Replacer.ReplaceValue,{"EndDate"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Values1",{{"HcmWorker_FK_PersonnelNumber", Order.Ascending},{"StartDateLeave", Order.Descending},{"EndDate", Order.Descending}}),
    #"Custom Grouping" = Table.Group(#"Sorted Rows" , {"HcmWorker_FK_PersonnelNumber"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Custom Grouping", "Partition", {"HcmLeaveType_FK_LeaveTypeID", "CompanyInfo_FK_DataArea", "HcmEmployment_FK_ValidFrom", "StartDateLeave", "CompanyInfo_FK_PartyNumber", "EndDate", "HcmEmployment_FK_ValidTo", "Index"}, {"HcmLeaveType_FK_LeaveTypeID", "CompanyInfo_FK_DataArea", "HcmEmployment_FK_ValidFrom", "StartDateLeave", "CompanyInfo_FK_PartyNumber", "EndDate", "HcmEmployment_FK_ValidTo", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Partition", "New ID", each "AB"),
    #"Sorted Rows1" = Table.Sort(#"Added Custom1",{{"Index", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows1", each ([Index] = 1)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Index letters", each if [Index] = 1 then "A" else if [Index] = 2 then "B" else ""),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"HcmWorker_FK_PersonnelNumber", "HcmLeaveType_FK_LeaveTypeID"})
in
    #"Removed Other Columns"






Workers Code
let
    Source = Excel.Workbook(File.Contents("C:\Users\tcoe\Documents\Emp Masters Issue.xlsx"), null, true),
    Workers_Sheet = Source{[Item="Workers",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Workers_Sheet, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "New ID", each "AB"),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"PersonnelNumber"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Other Columns",{"PersonnelNumber"},#"Emp Leaves Latest",{"HcmWorker_FK_PersonnelNumber"},"Query1",JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"HcmLeaveType_FK_LeaveTypeID"}, {"Query1.HcmLeaveType_FK_LeaveTypeID"})
in
    #"Expanded Query1"

 

CheenuSing Super Contributor
Super Contributor

Re: Merge returning non existent values

Hi @Tcoe,

 

I am unable to download the excel file.  Can you upload it on google drive and share the link.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Tcoe Frequent Visitor
Frequent Visitor

Re: Merge returning non existent values

Hi  CheenuSing,

 

Apologies for the delay in getting you this revised link!

 

Please find it here

 

https://drive.google.com/open?id=0B5coafxgNnpFNmktWXZFeXdnU2M

 

Any further questions please don't hesitate to ask.

 

Many Thanks

 

Trystan

MarcelBeug Super Contributor
Super Contributor

Re: Merge returning non existent values

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

 

Specializing in Power Query Formula Language (M)
Tcoe Frequent Visitor
Frequent Visitor

Re: Merge returning non existent values

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 41 members 1,044 guests