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

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"