Forum Discussion
How to do a join with multiple evaluation criteria in power query or M?
Hi! I'm developing a callback dashboard and the challenge is the following, I have a table of incoming calls and another table with phones that must be purged. I need to identify which cases sho...
Mahesh0016
Super User
Super UserAnonymous Can you share sample output in table format?
Incoming call table
id phone_number channel date 1 9992365b LEVEL 1 11/07/2022 2 992b6576 LEVEL 1 11/07/2022 3 99834738 LEVEL 1 11/07/2022 4 99375454 LEVEL 1 11/07/2022 5 99b29a86 LEVEL 1 11/07/2022 6 9943377a LEVEL 1 11/07/2022 7 99957b85 LEVEL 1 11/07/2022 8 99957b85 LEVEL 1 11/07/2022 9 99957b85 LEVEL 1 11/07/2022 10 993a9aaa LEVEL 1 01/08/2022 11 993a9aaa LEVEL 1 01/08/2022 12 993a9aaa LEVEL 1 03/10/2022 13 99834528 LEVEL 1 05/07/2022 14 99834528 LEVEL 1 05/07/2022 15 99834528 LEVEL 1 05/07/2022 16 99988a65 SAC 21/10/2022 17 99988a65 SAC 21/10/2022 18 99988a65 SAC 21/10/2022 19 99437b43 LEVEL 1 04/07/2022 20 99437b43 LEVEL 1 04/07/2022 21 99437b43 LEVEL 1 04/07/2022 Purges table
phone_number channel initial_date final_date 99375454 LEVEL 1 01/08/2022 993a9aaa LEVEL 1 01/01/2020 99437b43 LEVEL 1 01/01/2020 99988a65 LEVEL 1 01/02/2021 99437b43 LEVEL 1 01/07/2022 99834528 LEVEL 1 01/07/2022 9943377a LEVEL 1 01/10/2022 99437b43 LEVEL 1 01/05/2022 99b29a86 LEVEL 1 01/09/2022 99834528 SAC 01/10/2022 99834528 RETENTION 01/10/2022 993a9aaa SAC 01/12/2022 993a9aaa RETENTION 01/12/2022 99437b43 SAC 01/01/2020 99957b85 SAC 01/01/2020 99437b43 LEVEL 2 01/01/2020 99957b85 LEVEL 2 01/01/2020 99988a65 SAC 01/02/2021 99988a65 SAC EPV 01/02/2021 99437b43 SAC 01/07/2022 99834528 SAC 01/07/2022 99988a65 SAC 01/10/2022 99988a65 RETENTION 01/10/2022 Merged incoming calls table (by phone_number and channel)
id phone_number channel date initial_date final_date 4 99375454 LEVEL 1 11/07/2022 01/08/2022 10 993a9aaa LEVEL 1 01/08/2022 01/01/2020 11 993a9aaa LEVEL 1 01/08/2022 01/01/2020 12 993a9aaa LEVEL 1 03/10/2022 01/01/2020 19 99437b43 LEVEL 1 04/07/2022 01/01/2020 19 99437b43 LEVEL 1 04/07/2022 01/07/2022 19 99437b43 LEVEL 1 04/07/2022 01/05/2022 20 99437b43 LEVEL 1 04/07/2022 01/01/2020 20 99437b43 LEVEL 1 04/07/2022 01/07/2022 20 99437b43 LEVEL 1 04/07/2022 01/05/2022 21 99437b43 LEVEL 1 04/07/2022 01/01/2020 21 99437b43 LEVEL 1 04/07/2022 01/07/2022 21 99437b43 LEVEL 1 04/07/2022 01/05/2022 5 99b29a86 LEVEL 1 11/07/2022 01/09/2022 6 9943377a LEVEL 1 11/07/2022 01/10/2022 13 99834528 LEVEL 1 05/07/2022 01/07/2022 14 99834528 LEVEL 1 05/07/2022 01/07/2022 15 99834528 LEVEL 1 05/07/2022 01/07/2022 16 99988a65 SAC 21/10/2022 01/02/2021 16 99988a65 SAC 21/10/2022 01/10/2022 17 99988a65 SAC 21/10/2022 01/02/2021 17 99988a65 SAC 21/10/2022 01/10/2022 18 99988a65 SAC 21/10/2022 01/02/2021 18 99988a65 SAC 21/10/2022 01/10/2022 1 9992365b LEVEL 1 11/07/2022 2 992b6576 LEVEL 1 11/07/2022 3 99834738 LEVEL 1 11/07/2022 7 99957b85 LEVEL 1 11/07/2022 8 99957b85 LEVEL 1 11/07/2022 9 99957b85 LEVEL 1 11/07/2022 Merged table in treatment
id phone_number channel date initial_date final_date classification action comment 1 9992365b LEVEL 1 11/07/2022 common call keep in base it was not found in the purges base, therefore it must be 10 993a9aaa LEVEL 1 01/08/2022 01/01/2020 purge keep in base call within the period 11 993a9aaa LEVEL 1 01/08/2022 01/01/2020 purge keep in base call within the period 12 993a9aaa LEVEL 1 03/10/2022 01/01/2020 purge keep in base call within the period 13 99834528 LEVEL 1 05/07/2022 01/07/2022 purge keep in base call within the period 14 99834528 LEVEL 1 05/07/2022 01/07/2022 purge keep in base call within the period 15 99834528 LEVEL 1 05/07/2022 01/07/2022 purge keep in base call within the period 16 99988a65 SAC 21/10/2022 01/02/2021 purge exclude from base call within the period 16 99988a65 SAC 21/10/2022 01/10/2022 purge keep in base call within the period, most recent 17 99988a65 SAC 21/10/2022 01/02/2021 purge exclude from base call within the period 17 99988a65 SAC 21/10/2022 01/10/2022 purge keep in base call within the period, most recent 18 99988a65 SAC 21/10/2022 01/02/2021 purge exclude from base call within the period 18 99988a65 SAC 21/10/2022 01/10/2022 purge keep in base call within the period, most recent 19 99437b43 LEVEL 1 04/07/2022 01/01/2020 purge exclude from base call within the period 19 99437b43 LEVEL 1 04/07/2022 01/07/2022 purge keep in base call within the period, most recent 19 99437b43 LEVEL 1 04/07/2022 01/05/2022 purge exclude from base call within the period 2 992b6576 LEVEL 1 11/07/2022 common call keep in base it was not found in the purges base, therefore it must be Treated merged table
id phone_number channel date initial_date final_date classification 1 9992365b LEVEL 1 11/07/2022 common call 10 993a9aaa LEVEL 1 01/08/2022 01/01/2020 purge 11 993a9aaa LEVEL 1 01/08/2022 01/01/2020 purge 12 993a9aaa LEVEL 1 03/10/2022 01/01/2020 purge 13 99834528 LEVEL 1 05/07/2022 01/07/2022 purge 14 99834528 LEVEL 1 05/07/2022 01/07/2022 purge 15 99834528 LEVEL 1 05/07/2022 01/07/2022 purge 16 99988a65 SAC 21/10/2022 01/10/2022 purge 17 99988a65 SAC 21/10/2022 01/10/2022 purge 18 99988a65 SAC 21/10/2022 01/10/2022 purge 19 99437b43 LEVEL 1 04/07/2022 01/07/2022 purge 2 992b6576 LEVEL 1 11/07/2022 common call I hope it helps you understand