Hello Sir,
Below is the sample data and M code from advance editor.
let
Source = Csv.Document(File.Contents("C:\data1\Data\Desktop\Suresh TTBU\Ice_Off\Funnel_Report_Query\Working Files\Report_9-3-2021 10-55-51 PM_.txt"),[Delimiter="¿", Columns=68, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Use First Row as Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Change Type" = Table.TransformColumnTypes(#"Use First Row as Headers",{{"CandTxn_ID", Int64.Type}, {"WorkDay Id", type text}, {"Recruiter Name", type text}, {"RecEmployee_ID", Int64.Type}, {"Cand_name", type text}, {"CAND_NUMBER", Int64.Type}, {"Assessment_Name", type text}, {"Autoview_Fresher_Final_Score", type text}, {"Autoview_Experienced_Final_Score", type text}, {"Email Hirepro", type text}, {"Email AmCat", type text}, {"AMCAT_ID", Int64.Type}, {"Source", type text}, {"HirePro Sub Source", type text}, {"Amcat Sub Source", type text}, {"AgencyOrVendorName", type text}, {"Refree Emp Id", type text}, {"specSource", type text}, {"Offer Date", type text}, {"DOJ", type text}, {"Process", type text}, {"Profile", type text}, {"Interview_Status", type text}, {"Assessment_Status", type text}, {"TICKET_STATUS", type text}, {"NCC_Score", type text}, {"Final_Status", type text}, {"Joining_Status", type text}, {"AMCAT_ID_SVAR", type text}, {"SVAR Total Score", type text}, {"AMCAT ID Writex", type text}, {"WriteX_Total_Score", type text}, {"AMCAT ID GAT", type text}, {"Numerical Ability Score GAT", type text}, {"Logical and Analytical Reasoning Score GAT", type text}, {"Semi Tech Score GAT", type text}, {"Grammar Score GAT", type text}, {"Assessment Attempt Time", type datetime}, {"Assessment End Time", type datetime}, {"Candidate Status", type text}, {"Data Received date", type datetime}, {"FSR Date", type datetime}, {"Login Site", type text}, {"Login Location", type text}, {"Hiring_Location", type text}, {"PreferredCity", type text}, {"State", type text}, {"FSR_Remarks", type text}, {"FSR_Status", type text}, {"EducationBackground", type text}, {"Proposed_Process", type text}, {"JRF_Number", type text}, {"OfferExtend_Date", type text}, {"Rec_Tag_time", type datetime}, {"Ops_Stat", type text}, {"Ops_Assessment_Date", type text}, {"WorkExperience", type text}, {"processAppliedFor", type text}, {"FSR_Rejected_Reason", type text}, {"Rejection_Reason", type text}, {"SVAR_Conversational_Spoken_English_U_S_Total_Score", type number}, {"SVAR_Conversational_Spoken_English_U_S_CEFR_Level", type text}, {"Schedule Date", type datetime}, {"haveBroadband", type text}, {"haveLaptopAtHome", type text}, {"vaccinated", type text}, {"dose1", type text}, {"dose2", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Change Type", {"Source"}, #"Source Mapping", {"Source"}, "Source Mapping", JoinKind.LeftOuter),
#"Expanded Source Mapping" = Table.ExpandTableColumn(#"Merged Queries", "Source Mapping", {"Channel Mix"}, {"Source Mapping.Channel Mix"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Source Mapping", "Recruiter_Tagged", each if [Recruiter Name] = "" then "No" else "Yes"),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Recruiter_Tagged", type text}, {"Assessment_Status", type text}}),
#"Merged Queries1" = Table.NestedJoin(#"Changed Type", {"Interview_Status", "Assessment_Status", "Recruiter_Tagged"}, Disposition, {"Interview_Status", "Assessment_Status", "Recruiter_Tagged"}, "Disposition", JoinKind.LeftOuter),
#"Expanded Disposition" = Table.ExpandTableColumn(#"Merged Queries1", "Disposition", {"Final Status"}, {"Disposition.Final Status"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Disposition","(","",Replacer.ReplaceText,{"WorkDay Id"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","12th","Undergraduate",Replacer.ReplaceText,{"EducationBackground"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Post Grauate or Higher","Post Graduate or Higher",Replacer.ReplaceText,{"EducationBackground"}),
#"Added Conditional Column1" = Table.AddColumn(#"Replaced Value2", "Total Experience", each if [WorkExperience] = "0 - 6" then "0 - 6 months" else if [WorkExperience] = "0 - 6 months" then "0 - 6 months" else if [WorkExperience] = "12 - 24" then "12 - 24 months" else if [WorkExperience] = "12 - 24 months" then "12 - 24 months" else if [WorkExperience] = "6 - 12" then "6 - 12 months" else if [WorkExperience] = "6 - 12 months" then "6 - 12 months" else if [WorkExperience] = ">24 months" then "greater than 24" else if [WorkExperience] = "greater than 24" then "greater than 24" else "-"),
#"Replaced Value3" = Table.ReplaceValue(#"Added Conditional Column1","","Advisor - Non Voice",Replacer.ReplaceValue,{"processAppliedFor"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Advisor Customer Service - Non-Voice","Advisor - Non-Voice",Replacer.ReplaceText,{"processAppliedFor"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Advisor Customer Service - Voice","Advisor - Voice",Replacer.ReplaceText,{"processAppliedFor"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Advisor Technical Support - Non Voice","Technical Support - Non Voice",Replacer.ReplaceText,{"processAppliedFor"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Advisor Technical Support - Voice","Technical Support - Voice",Replacer.ReplaceText,{"processAppliedFor"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7",")","",Replacer.ReplaceText,{"WorkDay Id"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","'","",Replacer.ReplaceText,{"WorkDay Id"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value9",{{"WorkDay Id", Int64.Type}}),
#"Merged Queries2" = Table.NestedJoin(#"Changed Type1", {"WorkDay Id"}, #"Onboarding Report", {"Candidate ID"}, "Onboarding Report", JoinKind.LeftOuter),
#"Expanded Onboarding Report" = Table.ExpandTableColumn(#"Merged Queries2", "Onboarding Report", {"Joining Status"}, {"Onboarding Report.Joining Status"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Onboarding Report",{{"Onboarding Report.Joining Status", "Joining Status"}}),
#"Added Conditional Column2" = Table.AddColumn(#"Renamed Columns", "Offer_Status", each if [Offer Date] = "" then "Not Offered" else "Offered"),
#"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column2",{{"Joining Status", "Hire_Status"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns1",{{"DOJ", type datetime}, {"Offer Date", type datetime}}),
#"Extracted Date" = Table.TransformColumns(#"Changed Type2",{{"DOJ", DateTime.Date, type date}, {"Offer Date", DateTime.Date, type date}, {"Assessment Attempt Time", DateTime.Date, type date}, {"Assessment End Time", DateTime.Date, type date}, {"Data Received date", DateTime.Date, type date}, {"FSR Date", DateTime.Date, type date}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Extracted Date",{{"OfferExtend_Date", type datetime}}),
#"Extracted Date1" = Table.TransformColumns(#"Changed Type4",{{"OfferExtend_Date", DateTime.Date, type date}, {"Rec_Tag_time", DateTime.Date, type date}}),
#"Changed Type5" = Table.TransformColumnTypes(#"Extracted Date1",{{"Ops_Assessment_Date", type datetime}}),
#"Extracted Date2" = Table.TransformColumns(#"Changed Type5",{{"Ops_Assessment_Date", DateTime.Date, type date}, {"Schedule Date", DateTime.Date, type date}}),
#"Added Custom" = Table.AddColumn(#"Extracted Date2", "Final_Joining_Status", each if[Hire_Status]="Hired" and [Offer_Status]="Offered" then "Hired"
else if [Offer_Status]="Offered" and [DOJ]>= Date.From(DateTime.LocalNow()) then "Future Offer"
else if [Offer_Status]="Offered" and [DOJ]< Date.From(DateTime.LocalNow()) then "Not Joined"
else "Not Offered"),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "Assessment End Time", "Assessment End Time - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column",{{"Assessment End Time - Copy", "Week Num"}}),
#"Calculated Week of Month" = Table.TransformColumns(#"Renamed Columns2",{{"Week Num", Date.WeekOfMonth, Int64.Type}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Calculated Week of Month", "Assessment End Time", "Assessment End Time - Copy"),
#"Renamed Columns3" = Table.RenameColumns(#"Duplicated Column1",{{"Assessment End Time - Copy", "Assessment End Month"}}),
#"Extracted Month Name" = Table.TransformColumns(#"Renamed Columns3", {{"Assessment End Month", each Date.MonthName(_), type text}}),
#"Renamed Columns4" = Table.RenameColumns(#"Extracted Month Name",{{"Week Num", "Assessment End Week"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns4", "BYOD_Status", each if[haveBroadband]="Yes"and [haveLaptopAtHome]="Yes" then "Has Broadband & Laptop"
else if[haveBroadband]="Yes"and [haveLaptopAtHome]="No" then "Only Broadband"
else if[haveBroadband]="No"and [haveLaptopAtHome]="Yes" then "Only Laptop"
else if[haveBroadband]="No"and [haveLaptopAtHome]="No" then "No Broadband & Laptop" else "NA"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Vaccination_Status", each if[vaccinated]="Yes" and [dose1]="Yes" and [dose2]="Yes" then "Fully Vaccinated"
else if[vaccinated]="Yes" and [dose1]="Yes" and [dose2]="No" then "Partially Vaccinated"
else if[vaccinated]="Yes" and [dose1]="" and [dose2]="No" then "Partially Vaccinated"
else if[vaccinated]="Yes" and [dose1]="" and [dose2]="Yes" then "Fully Vaccinated"
else if[vaccinated]="Yes" and [dose1]="" and [dose2]="" then "Partially Vaccinated"
else if[vaccinated]="No" then "Not Vaccinated"
else "Not Available"),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"CandTxn_ID", "WorkDay Id", "Recruiter Name", "RecEmployee_ID", "Cand_name", "CAND_NUMBER", "Assessment_Name", "Autoview_Fresher_Final_Score", "Autoview_Experienced_Final_Score", "Email Hirepro", "Email AmCat", "AMCAT_ID", "Source", "HirePro Sub Source", "Amcat Sub Source", "AgencyOrVendorName", "Refree Emp Id", "specSource", "Offer Date", "DOJ", "Process", "Profile", "Interview_Status", "Assessment_Status", "TICKET_STATUS", "NCC_Score", "Final_Status", "Joining_Status", "AMCAT_ID_SVAR", "SVAR Total Score", "AMCAT ID Writex", "WriteX_Total_Score", "AMCAT ID GAT", "Numerical Ability Score GAT", "Logical and Analytical Reasoning Score GAT", "Semi Tech Score GAT", "Grammar Score GAT", "Assessment Attempt Time", "Assessment End Time", "Candidate Status", "Data Received date", "FSR Date", "Login Site", "Login Location", "Hiring_Location", "PreferredCity", "State", "FSR_Remarks", "FSR_Status", "EducationBackground", "Proposed_Process", "JRF_Number", "OfferExtend_Date", "Rec_Tag_time", "Ops_Stat", "Ops_Assessment_Date", "WorkExperience", "processAppliedFor", "FSR_Rejected_Reason", "Rejection_Reason", "SVAR_Conversational_Spoken_English_U_S_Total_Score", "SVAR_Conversational_Spoken_English_U_S_CEFR_Level", "Schedule Date", "haveBroadband", "haveLaptopAtHome", "vaccinated", "dose1", "dose2", "Source Mapping.Channel Mix", "Recruiter_Tagged", "Disposition.Final Status", "Total Experience", "Final_Joining_Status", "Assessment End Week", "Assessment End Month", "BYOD_Status", "Vaccination_Status"}),
#"Merged Queries3" = Table.NestedJoin(#"Removed Other Columns", {"PreferredCity"}, Zone_Mapping, {"Preferred_Location"}, "Zone_Mapping", JoinKind.LeftOuter),
#"Expanded Zone_Mapping" = Table.ExpandTableColumn(#"Merged Queries3", "Zone_Mapping", {"Zone"}, {"Zone_Mapping.Zone"}),
#"Renamed Columns5" = Table.RenameColumns(#"Expanded Zone_Mapping",{{"Zone_Mapping.Zone", "Preferred_Zone"}}),
#"Merged Queries4" = Table.NestedJoin(#"Renamed Columns5", {"AMCAT_ID"}, Demo_File, {"AMCAT_ID"}, "Demo_File", JoinKind.LeftAnti),
#"Inserted Day" = Table.AddColumn(#"Merged Queries4", "Day", each Date.Day([Assessment End Time]), Int64.Type),
#"Renamed Columns6" = Table.RenameColumns(#"Inserted Day",{{"Day", "Date_Only"}}),
#"Added Conditional Column3" = Table.AddColumn(#"Renamed Columns6", "Week Number", each if [Date_Only] <= 7 then "Week - 1" else if [Date_Only] <= 14 then "Week - 2" else if [Date_Only] <= 21 then "Week - 3" else "Week - 4&5"),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column3",{"Assessment End Week"}),
#"Merged Queries5" = Table.NestedJoin(#"Removed Columns", {"AMCAT_ID"}, NCC_Evaluation_Sheet, {"AMCAT ID"}, "NCC_Evaluation_Sheet", JoinKind.LeftOuter),
#"Expanded NCC_Evaluation_Sheet" = Table.ExpandTableColumn(#"Merged Queries5", "NCC_Evaluation_Sheet", {"VNA Assessor", "Evaluation Status", "Score Bucket", "Reason for Disqualified"}, {"NCC_Evaluation_Sheet.VNA Assessor", "NCC_Evaluation_Sheet.Evaluation Status", "NCC_Evaluation_Sheet.Score Bucket", "NCC_Evaluation_Sheet.Reason for Disqualified"})
in
#"Expanded NCC_Evaluation_Sheet"
Merge Join Type - Left Outer
Sample data mailed you.
Suresh
