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 should be purged from the incoming calls table without generating duplicates.
The purges table can contain the same phone several times, in different channels or dates, so when I do the "merge" in the power bi, some phones are duplicated and I am not able to remove the duplicates taking into account the rules.
First of all the tables look like these:
*The phone numbers, as well as the channels were originally replaced by random values, generated from the Excel "random" formula.
Incoming call table:
| id | date | phone_number | channel |
| 69819 | 01/07/2022 | 981485009 | CS |
| 69227 | 01/07/2022 | 991588762 | CS |
| 69536 | 01/07/2022 | 999408912 | CS |
| 69853 | 01/07/2022 | 999472894 | CS |
| 70018 | 01/07/2022 | 1120946054 | CS |
| 69558 | 01/07/2022 | 1122027100 | CS |
| 69540 | 01/07/2022 | 1122587241 | CS |
| 69629 | 01/07/2022 | 1124095781 | CS |
| 70093 | 01/07/2022 | 1124095781 | CS |
| 69911 | 01/07/2022 | 1124096723 | CS |
| 70116 | 01/07/2022 | 1125621333 | CS |
| 69370 | 01/07/2022 | 1125725400 | CS |
| 69669 | 01/07/2022 | 1126019574 | CS |
| 69287 | 01/07/2022 | 1126597208 | CS |
Purges table:
| phone_number | channel | initial_date | final_date |
| 30732524688599 | CS | 01/01/2020 | |
| 14337853219993 | CS | 01/01/2020 | |
| 59190721082293 | CS | 01/01/2020 | |
| 16283679073036 | CS | 01/01/2020 | |
| 47630830671103 | CS | 01/01/2020 | |
| 25328736777322 | CS | 01/01/2020 | |
| 54018118052795 | CS | 01/01/2020 | |
| 53195278252695 | CS | 01/01/2020 | |
| 57259967606207 | SAC | 01/01/2020 | |
| 7320107239150 | SAC | 01/01/2020 | |
| 49314065238063 | SAC | 01/01/2020 | |
| 16594429566012 | SAC | 01/01/2020 | |
| 16597967001819 | SAC | 01/01/2020 | |
| 88940702501287 | SAC | 01/01/2020 | |
| 7671555843556 | SAC | 01/01/2020 | |
| 6297551829108 | SAC | 01/01/2020 | |
| 19233336321891 | SAC | 01/01/2020 | |
| 13016669976935 | RECEPTIVE | 01/01/2020 | |
| 42839095059532 | RECEPTIVE | 01/01/2020 | |
| 16082650569259 | RECEPTIVE | 01/01/2020 | |
The rules are as follows:
- If a telephone is not in the purge table, I can keep it in the base, as it will not duplicate, I just need to insert the information that it is not a purge case, that is, it is a "common call".
- If for any telephone, only one record is found in the purge table, having the same channel both in the return of the purge table and in the received calls table and with the call date within the interval determined in the purge table, this is considered a purge case and should be kept as it will not duplicate. If the record is not from the same channel, we must also keep the case, but mark it as "ordinary call".
- If for any phone, more than 1 records are found in the purges table, we must do the following:
- Evaluate whether the records located contain any with the same channel.
- If yes, check if the date of the call is within the range found and if more than one record gives the same result, then we must choose the record with the most recent purge interval and finally mark the result as a purge case.
- If not, we must keep any of the lines, marking the call as "common call".
My idea would be, do it like in SQL, similar to the example below, but I don't know how to do something similar in power query or in M, can you help me?
select
c.phone_number,
c.channel,
c.date
from
calls c
join
purge p on
p.phone_number = c.phone_number and
p.channel = c.channel and
(case
when final_date is null then
case
when p.initial_date <= c.date then 1
else 0
end
else
case
when c.date between p.initial_date and p.final_date then 1
else 0
end
end)=1
Sorry for the mess in the SQL code, I couldn't adjust the text editing on the site.
