Power Query Help

%3CLINGO-SUB%20id%3D%22lingo-sub-3497955%22%20slang%3D%22en-US%22%3EPower%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3497955%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20following%20data%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELegal%20NameFacilityLIQ%20Origination%20DateLIQ%20Maturity%20DateAdjusted%20Origination%20DateAdjusted%20Maturity%20Date%3C%2FP%3E%3CTABLE%20width%3D%22784%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EMM%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%2F28%2F2017%3C%2FTD%3E%3CTD%3E8%2F31%2F2022%3C%2FTD%3E%3CTD%3E8%2F31%2F2022%3C%2FTD%3E%3CTD%3E8%2F31%2F2022%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EKO%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E3%2F5%2F2014%3C%2FTD%3E%3CTD%3E12%2F31%2F2023%3C%2FTD%3E%3CTD%3E6%2F30%2F2020%3C%2FTD%3E%3CTD%3E6%2F30%2F2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EKO%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E3%2F5%2F2014%3C%2FTD%3E%3CTD%3E12%2F31%2F2023%3C%2FTD%3E%3CTD%3E6%2F30%2F2021%3C%2FTD%3E%3CTD%3E12%2F31%2F2023%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMD%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E12%2F29%2F2021%3C%2FTD%3E%3CTD%3E12%2F29%2F2026%3C%2FTD%3E%3CTD%3E12%2F29%2F2026%3C%2FTD%3E%3CTD%3E12%2F29%2F2026%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20I%20want%20result%20in%20following%20format%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDesired%20Output%3C%2FP%3E%3CP%3ELegal%20NameFacilityLIQ%20Origination%20DateLIQ%20Maturity%20DateAdjusted%20Origination%20DateAdjusted%20Maturity%20Date%3C%2FP%3E%3CTABLE%20width%3D%22783px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2270.9px%22%3EMM%3C%2FTD%3E%3CTD%20width%3D%2269.9px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22157.788px%22%3E2%2F28%2F2017%3C%2FTD%3E%3CTD%20width%3D%22162.788px%22%3E8%2F31%2F2022%3C%2FTD%3E%3CTD%20width%3D%22157.788px%22%3E2%2F28%2F2017%3C%2FTD%3E%3CTD%20width%3D%22162.837px%22%3E8%2F31%2F2022%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2270.9px%22%3EKO%3C%2FTD%3E%3CTD%20width%3D%2269.9px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22157.788px%22%3E3%2F5%2F2014%3C%2FTD%3E%3CTD%20width%3D%22162.788px%22%3E12%2F31%2F2023%3C%2FTD%3E%3CTD%20width%3D%22157.788px%22%3E3%2F5%2F2014%3C%2FTD%3E%3CTD%20width%3D%22162.837px%22%3E6%2F30%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2270.9px%22%3EKO%3C%2FTD%3E%3CTD%20width%3D%2269.9px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22157.788px%22%3E3%2F5%2F2014%3C%2FTD%3E%3CTD%20width%3D%22162.788px%22%3E12%2F31%2F2023%3C%2FTD%3E%3CTD%20width%3D%22157.788px%22%3E6%2F30%2F2020%3C%2FTD%3E%3CTD%20width%3D%22162.837px%22%3E6%2F30%2F2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2270.9px%22%3EKO%3C%2FTD%3E%3CTD%20width%3D%2269.9px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22157.788px%22%3E3%2F5%2F2014%3C%2FTD%3E%3CTD%20width%3D%22162.788px%22%3E12%2F31%2F2023%3C%2FTD%3E%3CTD%20width%3D%22157.788px%22%3E6%2F30%2F2021%3C%2FTD%3E%3CTD%20width%3D%22162.837px%22%3E12%2F31%2F2023%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2270.9px%22%3EMD%3C%2FTD%3E%3CTD%20width%3D%2269.9px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22157.788px%22%3E12%2F29%2F2021%3C%2FTD%3E%3CTD%20width%3D%22162.788px%22%3E12%2F29%2F2026%3C%2FTD%3E%3CTD%20width%3D%22157.788px%22%3E12%2F29%2F2021%3C%2FTD%3E%3CTD%20width%3D%22162.837px%22%3E12%2F29%2F2026%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eor%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20some%20error%20in%20my%20working%20.%20I%20am%20attaching%20my%20working%20.Could%20anyone%20advise%20how%20to%20get%20desired%20result%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3497955%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3498563%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3498563%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1371826%22%20target%3D%22_blank%22%3E%40Cmpunk%3C%2FA%3E%26nbsp%3BHave%20a%20look%20at%20the%20attached%20file.%20I%20added%20another%20query%20Merge2%20that%20seems%20to%20produce%20what%20you%20asked%20for.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20style%3D%22width%3A%20560px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F379717i72ECBEFE35FADD12%2Fimage-dimensions%2F560x126%3Fv%3Dv2%22%20width%3D%22560%22%20height%3D%22126%22%20role%3D%22button%22%20title%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20alt%3D%22Riny_van_Eekelen_0-1655102757137.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3499782%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3499782%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Thank%20you%20for%20your%20response.%20I%20tried%20your%20solution%20on%20my%20sample%20data%20unfortunately%20it%20is%20not%20giving%20desired%20result.%20Could%20you%20advise%3F%20I%20am%20attaching%20file%20for%20your%20reference.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3500466%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3500466%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1371826%22%20target%3D%22_blank%22%3E%40Cmpunk%3C%2FA%3E%26nbsp%3BBut%20you%20didn't%20use%20the%20same%20query.%20I%20note%20you%20don't%20have%20the%20%22Remove%20Duplicates%22%20step%2C%20for%20instance.%20May%20I%20suggest%20you%20copy%20your%20%22real%22%20data%20into%20the%20relevant%20tables%20in%20the%20file%20I%20sent.%20Not%20changing%20any%20table%20or%20column%20names.%20Don't%20have%20time%20to%20that%20myself%20right%20now%2C%20sorry.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3500850%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3500850%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Thanks%20for%20your%20prompt%20response.%20I%20tried%20to%20copy%20my%20real%20data%20and%20use%20your%20solution.%20Your%20solution%20works%20very%20well%20however%20there%20are%20instances%20when%20it%20fails.%20I%20am%20attaching%20one%20such%20example%20.see%20attachment%20there%20are%20two%20queries%20tables%205%20%26amp%3B%206%20and%20merge%203%20final%20solution.%20Thank%20you%20once%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3502734%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3502734%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20response.%20I%20understand%20your%20thoughts.%20Your%20solution%20is%20flawless%20but%20for%20some%20of%20the%20securities%2C%20solution%20is%20not%20working.%20Whenever%20you%20have%20time%20kindly%20guide%20me.%20The%20securities%20are%20part%20of%20the%20same%20dataset%20so%20solution%20would%20work%20for%20all%20the%20securities.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3503209%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3503209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1371826%22%20target%3D%22_blank%22%3E%40Cmpunk%3C%2FA%3E%26nbsp%3BSorry%2C%20but%20the%20data%20in%20the%20file%20you%20attached%20is%20not%20resembling%20the%20date%20I%20had%20based%20my%20queries%20on.%20The%20first%20table%20is%20OK%20but%20the%20second%20one%20doesn't%20have%20the%20columns%20needed%20to%20achieve%20what%20you%20described%20earlier.%26nbsp%3B%3C%2FP%3E%3CP%3EFurthermore%2C%20you%20wrote%20that%20it%20worked%20for%20most%20but%20not%20all.%20Your%20file%20links%20to%20workbooks%20on%20your%20own%20machine%20and%20I%20can%20only%20guess%20that%20the%20tables%20in%20Sheet1%20represent%20what's%20in%20these%20workbooks.%20But%20maybe%2C%20they%20don't.%20If%20they%20do%2C%20I%20suggest%20you%20connect%20the%20queries%20to%20these%20tables%2C%20load%20the%20Merge%20query%20back%20to%20Excel%20and%20explain%20what's%20wrong%20and%20why.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3508433%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3508433%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20mail%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20215%2C315%2C307%2C258%2C336%2C190%2C420%2C506%2C151%2C232%2C541-%20Row%20Bifurcation%20is%20incorrect.%20Example%20for%20215%20correct%20output%20is%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E11%2F13%2F2015-3%2F31%2F2021%3C%2FP%3E%3CP%3E3%2F31%2F2021-4%2F30%2F2021%3C%2FP%3E%3CP%3E4%2F30%2F2021-12%2F31%2F2022%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOutput%20as%20per%20sheet%3C%2FP%3E%3CTABLE%20width%3D%22348%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22182%22%3E3%2F31%2F2021%3C%2FTD%3E%3CTD%20width%3D%22166%22%3E3%2F31%2F2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%2F30%2F2021%3C%2FTD%3E%3CTD%3E4%2F30%2F2021%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%2F31%2F2022%3C%2FTD%3E%3CTD%3E12%2F31%2F2022%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20506%20Correct%20Output%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E8%2F6%2F2019-3%2F1%2F2022%3C%2FP%3E%3CP%3E3%2F1%2F2022-3%2F11%2F2022%3C%2FP%3E%3CP%3E3%2F11%2F2022-12%2F31%2F2022%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOutput%20as%20per%20sheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22348%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22182%22%3E8%2F6%2F2019%3C%2FTD%3E%3CTD%20width%3D%22166%22%3E3%2F11%2F2022%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%2F1%2F2022%3C%2FTD%3E%3CTD%3E12%2F31%2F2022%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%2F11%2F2022%3C%2FTD%3E%3CTD%3E12%2F31%2F2022%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20173-%20One%20of%20the%20row%20is%20missing%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECorrect%20Output%3A%3C%2FP%3E%3CP%3E6%2F1%2F2015-12%2F23%2F2023%3C%2FP%3E%3CP%3E12%2F23%2F2023-12%2F23%2F2024%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOutput%20as%20per%20sheet%3A%3C%2FP%3E%3CTABLE%20width%3D%22348%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22182%22%3E6%2F1%2F2015%3C%2FTD%3E%3CTD%20width%3D%22166%22%3E12%2F23%2F2023%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20tinkering%20with%20the%20formula%20but%20to%20no%20avail.%20Any%20suggestion%20will%20be%20appreciated%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3508761%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3508761%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1371826%22%20target%3D%22_blank%22%3E%40Cmpunk%3C%2FA%3E%26nbsp%3BThank%20you!%20That%20was%20clear.%20I%20had%20misinterpreted%20on%20piece%20of%20your%20logic.%20Adjusted%20it%20in%20the%20attached%20sheet.%20I%20removed%26nbsp%3B%3CU%3E%3CEM%3E%2B1%3C%2FEM%3E%3C%2FU%3E%20in%20the%20%3CU%3E%3CEM%3Ethen%3C%2FEM%3E%3C%2FU%3E%20part%20of%20step%20%3CU%3E%3CEM%3ECustom3%3C%2FEM%3E%3C%2FU%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3510054%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3510054%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Thank%20you%20for%20your%20response.%20Please%20see%20below%20comments%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E117%2C215%2C107%2C336%2C242%2C370-%20I%20am%20still%20not%20getting%20correct%20order.%3C%2FP%3E%3CP%3EFor%20example%20117%20correct%20output%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3%2F14%2F2012-11%2F30%2F2020%3C%2FP%3E%3CP%3E11%2F30%2F2020-10%2F15%2F2021%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOutput%20as%20per%20sheet%3A%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22348%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22182%22%3E3%2F14%2F2012%3C%2FTD%3E%3CTD%20width%3D%22166%22%3E11%2F30%2F2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E11%2F30%2F2020%3C%2FTD%3E%3CTD%3E11%2F30%2F2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20more%26nbsp%3B%20example%20for%20370%20Output%20as%20per%20sheet%26nbsp%3B%20looks%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22348%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22182%22%3E11%2F6%2F2017%3C%2FTD%3E%3CTD%20width%3D%22166%22%3E4%2F30%2F2022%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%2F30%2F2022%3C%2FTD%3E%3CTD%3E4%2F30%2F2022%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2F31%2F2022%3C%2FTD%3E%3CTD%3E5%2F31%2F2022%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%2F3%2F2022%3C%2FTD%3E%3CTD%3E4%2F30%2F2022%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20173%20it%20is%20still%20not%20showing%20second%20row%20correct%20output%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E6%2F1%2F2015-12%2F23%2F23%3C%2FP%3E%3CP%3E12%2F23%2F23-12%2F23%2F24%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOutput%20as%20per%20sheet%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22348%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22182%22%3E6%2F1%2F2015%3C%2FTD%3E%3CTD%20width%3D%22166%22%3E12%2F23%2F2023%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%2F23%2F2023%3C%2FTD%3E%3CTD%3E12%2F23%2F2023%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELook%20into%20tab%206152022%20for%20more%20detail.%20PQ%20are%20named%20as%2011%2C12%20%26amp%3B%20Merge.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3510186%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3510186%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1371826%22%20target%3D%22_blank%22%3E%40Cmpunk%3C%2FA%3E%26nbsp%3BAgain%2C%20I%20can%20not%20work%20on%20a%20file%20that%20connects%20to%20something%20on%20your%20own%20system.%20So%2C%20I%20copied%20the%20data%20for%20117%20and%20370%20into%20my%20own%20file.%20173%20was%20already%20there.%20Then%20I%20refreshed%20my%20query.%3C%2FP%3E%3CP%3EOn%20117%2C%20note%20that%20your%20data%20contains%20three%20identical%20rows%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Riny_van_Eekelen_0-1655297862862.png%22%20style%3D%22width%3A%20641px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655297862862.png%22%20style%3D%22width%3A%20641px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655297862862.png%22%20style%3D%22width%3A%20641px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_0-1655297862862.png%22%20style%3D%22width%3A%20641px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F380571i8C7B30C1368527FF%2Fimage-dimensions%2F641x53%3Fv%3Dv2%22%20width%3D%22641%22%20height%3D%2253%22%20role%3D%22button%22%20title%3D%22Riny_van_Eekelen_0-1655297862862.png%22%20alt%3D%22Riny_van_Eekelen_0-1655297862862.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20query%20only%20picks-up%20one%20row%20and%20the%20end-result%20includes%20only%20one%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20both%20370%20and%20173%2C%20the%20end-results%20looks%20like%20this%20and%20I%20believe%20it%20follows%20the%20same%20logic%20as%20all%20the%20other%20ones.%20So%2C%20I%20don't%20really%20understand%20the%20problem.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Riny_van_Eekelen_1-1655298135070.png%22%20style%3D%22width%3A%20682px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_1-1655298135070.png%22%20style%3D%22width%3A%20682px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_1-1655298135070.png%22%20style%3D%22width%3A%20682px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Riny_van_Eekelen_1-1655298135070.png%22%20style%3D%22width%3A%20682px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F380573i281A96B7A3838EE6%2Fimage-dimensions%2F682x75%3Fv%3Dv2%22%20width%3D%22682%22%20height%3D%2275%22%20role%3D%22button%22%20title%3D%22Riny_van_Eekelen_1-1655298135070.png%22%20alt%3D%22Riny_van_Eekelen_1-1655298135070.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3512139%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3512139%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BI%20am%20sorry%20for%20going%20back%20and%20forth.%20After%20some%20analysis%2C%20I%20realized%20the%20reason%20the%20solution%20is%20not%20working%20is%20that%20after%20the%20RemoveDup1%20step%20we%20are%20using%20the%20Custom%203%20step%20in%20which%20we%20are%20referring%20to%20idx%20table%20in%20which%20there%20are%20multiple%20duplicate%20values%20hence%20match%20proper%20answer%20is%20not%20coming%20instead%20in%20Custom3%20step%20I%20want%20to%20refer%20RemovDup1%20step%20instead%20of%20Idx.%20Let%20me%20know%20if%20you%20need%20color%20on%20this.%26nbsp%3B%20Suppose%20if%20I%20refer%20to%20RemoveDup1%20in%20Custom3%20step%20then%20my%20indexing%20is%20going%20out%20for%20a%20toss%20since%20using%20duplicate%20I%20am%20deleting%20some%20of%20the%20rows.%20in%20the%20earlier%20step%20Any%20advice%20on%20what%20can%20be%20done%20to%20correct%20it%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20attaching%20a%20screenshots%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20four%20lines%20in%20Idx%20denotes%20security%20no%20107%20and%20first%203%20lines%20in%20Custom%203%20denotes%20107.%20Looking%20at%20it%20I%20hope%20you%20understand%20what%20I%20can%20trying%20to%20convey%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20desired%20output%20is%20%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3%2F5%2F14-6%2F30%2F2020%3C%2FP%3E%3CP%3E6%2F30%2F2020-6%2F30%2F2021%3C%2FP%3E%3CP%3E6%2F30%2F2021-12%2F31%2F2023%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECustom3%20Step%3A%3C%2FP%3E%3CP%3ETable.AddColumn(RemoveDup1%2C%20%22CheckNext%22%2C%20each%20try%3CBR%20%2F%3Eif%20%5BLegal%20Name%5D%20%3D%20Idx%5BLegal%20Name%5D%7B%5BIndex%5D%2B1%7D%3CBR%20%2F%3Ethen%20Idx%5BExtended%20Maturity%20Date%5D%7B%5BIndex%5D%7D%3CBR%20%2F%3Eelse%20null%3CBR%20%2F%3Eotherwise%20null)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have following data

 

Legal NameFacilityLIQ Origination DateLIQ Maturity DateAdjusted Origination DateAdjusted Maturity Date

MM12/28/20178/31/20228/31/20228/31/2022
KO23/5/201412/31/20236/30/20206/30/2021
KO23/5/201412/31/20236/30/202112/31/2023
MD312/29/202112/29/202612/29/202612/29/2026

 

However I want result in following format

 

Desired Output

Legal NameFacilityLIQ Origination DateLIQ Maturity DateAdjusted Origination DateAdjusted Maturity Date

MM12/28/20178/31/20222/28/20178/31/2022
KO23/5/201412/31/20233/5/20146/30/2020
KO23/5/201412/31/20236/30/20206/30/2021
KO23/5/201412/31/20236/30/202112/31/2023
MD312/29/202112/29/202612/29/202112/29/2026

 

or 

There is some error in my working . I am attaching my working .Could anyone advise how to get desired result?

10 Replies

@Cmpunk Have a look at the attached file. I added another query Merge2 that seems to produce what you asked for.

Riny_van_Eekelen_0-1655102757137.png

 

@Riny_van_Eekelen  Thank you for your response. I tried your solution on my sample data unfortunately it is not giving desired result. Could you advise? I am attaching file for your reference.

@Cmpunk But you didn't use the same query. I note you don't have the "Remove Duplicates" step, for instance. May I suggest you copy your "real" data into the relevant tables in the file I sent. Not changing any table or column names. Don't have time to that myself right now, sorry.

 

@Riny_van_Eekelen Thank you for your response. I understand your thoughts. Your solution is flawless but for some of the securities, solution is not working. Whenever you have time kindly guide me. The securities are part of the same dataset so solution would work for all the securities.

 

 

@Cmpunk Sorry, but the data in the file you attached is not resembling the date I had based my queries on. The first table is OK but the second one doesn't have the columns needed to achieve what you described earlier. 

Furthermore, you wrote that it worked for most but not all. Your file links to workbooks on your own machine and I can only guess that the tables in Sheet1 represent what's in these workbooks. But maybe, they don't. If they do, I suggest you connect the queries to these tables, load the Merge query back to Excel and explain what's wrong and why.

@Riny_van_Eekelen Thank you for your mail .

 

For 215,315,307,258,336,190,420,506,151,232,541- Row Bifurcation is incorrect. Example for 215 correct output is 

 

11/13/2015-3/31/2021

3/31/2021-4/30/2021

4/30/2021-12/31/2022

 

Output as per sheet

3/31/20213/31/2021
4/30/20214/30/2021
12/31/202212/31/2022

 

For 506 Correct Output

 

8/6/2019-3/1/2022

3/1/2022-3/11/2022

3/11/2022-12/31/2022

 

Output as per sheet:

 

8/6/20193/11/2022
3/1/202212/31/2022
3/11/202212/31/2022

 

For 173- One of the row is missing

 

Correct Output:

6/1/2015-12/23/2023

12/23/2023-12/23/2024

 

Output as per sheet:

6/1/201512/23/2023

 

 

I tried tinkering with the formula but to no avail. Any suggestion will be appreciated?

@Cmpunk Thank you! That was clear. I had misinterpreted on piece of your logic. Adjusted it in the attached sheet. I removed +1 in the then part of step Custom3

@Riny_van_Eekelen  Thank you for your response. Please see below comments:

 

117,215,107,336,242,370- I am still not getting correct order.

For example 117 correct output

 

3/14/2012-11/30/2020

11/30/2020-10/15/2021

 

Output as per sheet: 

3/14/201211/30/2020
11/30/202011/30/2020

 

One more  example for 370 Output as per sheet  looks wrong.

 

11/6/20174/30/2022
4/30/20224/30/2022
5/31/20225/31/2022
6/3/20224/30/2022

 

For 173 it is still not showing second row correct output 

 

6/1/2015-12/23/23

12/23/23-12/23/24

 

Output as per sheet:

 

6/1/201512/23/2023
12/23/202312/23/2023

 

Look into tab 6152022 for more detail. PQ are named as 11,12 & Merge.

@Cmpunk Again, I can not work on a file that connects to something on your own system. So, I copied the data for 117 and 370 into my own file. 173 was already there. Then I refreshed my query.

On 117, note that your data contains three identical rows:

Riny_van_Eekelen_0-1655297862862.png

The query only picks-up one row and the end-result includes only one row.

 

On both 370 and 173, the end-results looks like this and I believe it follows the same logic as all the other ones. So, I don't really understand the problem.

Riny_van_Eekelen_1-1655298135070.png

 

 

 

@Riny_van_Eekelen I am sorry for going back and forth. After some analysis, I realized the reason the solution is not working is that after the RemoveDup1 step we are using the Custom 3 step in which we are referring to idx table in which there are multiple duplicate values hence match proper answer is not coming instead in Custom3 step I want to refer RemovDup1 step instead of Idx. Let me know if you need color on this.  Suppose if I refer to RemoveDup1 in Custom3 step then my indexing is going out for a toss since using duplicate I am deleting some of the rows. in the earlier step Any advice on what can be done to correct it?

 

I am attaching a screenshots for you.

 

First four lines in Idx denotes security no 107 and first 3 lines in Custom 3 denotes 107. Looking at it I hope you understand what I can trying to convey?

 

As you can see desired output is :

 

3/5/14-6/30/2020

6/30/2020-6/30/2021

6/30/2021-12/31/2023

 

 

Custom3 Step:

Table.AddColumn(RemoveDup1, "CheckNext", each try
if [Legal Name] = Idx[Legal Name]{[Index]+1}
then Idx[Extended Maturity Date]{[Index]}
else null
otherwise null)