Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
joseagj
New Member

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 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:

iddatephone_numberchannel
6981901/07/2022981485009CS
6922701/07/2022991588762CS
6953601/07/2022999408912CS
6985301/07/2022999472894CS
7001801/07/20221120946054CS
6955801/07/20221122027100CS
6954001/07/20221122587241CS
6962901/07/20221124095781CS
7009301/07/20221124095781CS
6991101/07/20221124096723CS
7011601/07/20221125621333CS
6937001/07/20221125725400CS
6966901/07/20221126019574CS
6928701/07/20221126597208CS

 

Purges table:

phone_numberchannelinitial_datefinal_date
30732524688599CS01/01/2020 
14337853219993CS01/01/2020 
59190721082293CS01/01/2020 
16283679073036CS01/01/2020 
47630830671103CS01/01/2020 
25328736777322CS01/01/2020 
54018118052795CS01/01/2020 
53195278252695CS01/01/2020 
57259967606207SAC01/01/2020 
7320107239150SAC01/01/2020 
49314065238063SAC01/01/2020 
16594429566012SAC01/01/2020 
16597967001819SAC01/01/2020 
88940702501287SAC01/01/2020 
7671555843556SAC01/01/2020 
6297551829108SAC01/01/2020 
19233336321891SAC01/01/2020 
13016669976935RECEPTIVE01/01/2020 
42839095059532RECEPTIVE01/01/2020 
16082650569259RECEPTIVE01/01/2020 

 

The rules are as follows:

  1. 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".
  2. 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".
  3. If for any phone, more than 1 records are found in the purges table, we must do the following:
    1. Evaluate whether the records located contain any with the same channel.
    2. 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.
    3. 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.

4 REPLIES 4
KT_Bsmart2gethe
Impactful Individual
Impactful Individual

HI @joseagj ,

 

Hold control and click the column you want to merge. You can select multiple columns to merge to avoid duplicate values.

 

Regards

KT 

Thanks. But the problem is that of the three columns I need to relate, only two work this way. In the case of the date, I need the power query to bring me only the cases from the purges table that have periods compatible with the date of the call, present in the table of received calls.

Mahesh0016
Super User
Super User

@joseagj Can you share sample output in table format?

Incoming call table

idphone_numberchanneldate
19992365bLEVEL 111/07/2022
2992b6576LEVEL 111/07/2022
399834738LEVEL 111/07/2022
499375454LEVEL 111/07/2022
599b29a86LEVEL 111/07/2022
69943377aLEVEL 111/07/2022
799957b85LEVEL 111/07/2022
899957b85LEVEL 111/07/2022
999957b85LEVEL 111/07/2022
10993a9aaaLEVEL 101/08/2022
11993a9aaaLEVEL 101/08/2022
12993a9aaaLEVEL 103/10/2022
1399834528LEVEL 105/07/2022
1499834528LEVEL 105/07/2022
1599834528LEVEL 105/07/2022
1699988a65SAC21/10/2022
1799988a65SAC21/10/2022
1899988a65SAC21/10/2022
1999437b43LEVEL 104/07/2022
2099437b43LEVEL 104/07/2022
2199437b43LEVEL 104/07/2022

 

Purges table

phone_numberchannelinitial_datefinal_date
99375454LEVEL 101/08/2022 
993a9aaaLEVEL 101/01/2020 
99437b43LEVEL 101/01/2020 
99988a65LEVEL 101/02/2021 
99437b43LEVEL 101/07/2022 
99834528LEVEL 101/07/2022 
9943377aLEVEL 101/10/2022 
99437b43LEVEL 101/05/2022 
99b29a86LEVEL 101/09/2022 
99834528SAC01/10/2022 
99834528RETENTION01/10/2022 
993a9aaaSAC01/12/2022 
993a9aaaRETENTION01/12/2022 
99437b43SAC01/01/2020 
99957b85SAC01/01/2020 
99437b43LEVEL 201/01/2020 
99957b85LEVEL 201/01/2020 
99988a65SAC01/02/2021 
99988a65SAC EPV01/02/2021 
99437b43SAC01/07/2022 
99834528SAC01/07/2022 
99988a65SAC01/10/2022 
99988a65RETENTION01/10/2022 

 

Merged incoming calls table (by phone_number and channel)

idphone_numberchanneldateinitial_datefinal_date
499375454LEVEL 111/07/202201/08/2022 
10993a9aaaLEVEL 101/08/202201/01/2020 
11993a9aaaLEVEL 101/08/202201/01/2020 
12993a9aaaLEVEL 103/10/202201/01/2020 
1999437b43LEVEL 104/07/202201/01/2020 
1999437b43LEVEL 104/07/202201/07/2022 
1999437b43LEVEL 104/07/202201/05/2022 
2099437b43LEVEL 104/07/202201/01/2020 
2099437b43LEVEL 104/07/202201/07/2022 
2099437b43LEVEL 104/07/202201/05/2022 
2199437b43LEVEL 104/07/202201/01/2020 
2199437b43LEVEL 104/07/202201/07/2022 
2199437b43LEVEL 104/07/202201/05/2022 
599b29a86LEVEL 111/07/202201/09/2022 
69943377aLEVEL 111/07/202201/10/2022 
1399834528LEVEL 105/07/202201/07/2022 
1499834528LEVEL 105/07/202201/07/2022 
1599834528LEVEL 105/07/202201/07/2022 
1699988a65SAC21/10/202201/02/2021 
1699988a65SAC21/10/202201/10/2022 
1799988a65SAC21/10/202201/02/2021 
1799988a65SAC21/10/202201/10/2022 
1899988a65SAC21/10/202201/02/2021 
1899988a65SAC21/10/202201/10/2022 
19992365bLEVEL 111/07/2022  
2992b6576LEVEL 111/07/2022  
399834738LEVEL 111/07/2022  
799957b85LEVEL 111/07/2022  
899957b85LEVEL 111/07/2022  
999957b85LEVEL 111/07/2022  

 

Merged table in treatment

idphone_numberchanneldateinitial_datefinal_dateclassificationactioncomment
19992365bLEVEL 111/07/2022  common callkeep in baseit was not found in the purges base, therefore it must be
10993a9aaaLEVEL 101/08/202201/01/2020 purgekeep in basecall within the period
11993a9aaaLEVEL 101/08/202201/01/2020 purgekeep in basecall within the period
12993a9aaaLEVEL 103/10/202201/01/2020 purgekeep in basecall within the period
1399834528LEVEL 105/07/202201/07/2022 purgekeep in basecall within the period
1499834528LEVEL 105/07/202201/07/2022 purgekeep in basecall within the period
1599834528LEVEL 105/07/202201/07/2022 purgekeep in basecall within the period
1699988a65SAC21/10/202201/02/2021 purgeexclude from basecall within the period
1699988a65SAC21/10/202201/10/2022 purgekeep in basecall within the period, most recent
1799988a65SAC21/10/202201/02/2021 purgeexclude from basecall within the period
1799988a65SAC21/10/202201/10/2022 purgekeep in basecall within the period, most recent
1899988a65SAC21/10/202201/02/2021 purgeexclude from basecall within the period
1899988a65SAC21/10/202201/10/2022 purgekeep in basecall within the period, most recent
1999437b43LEVEL 104/07/202201/01/2020 purgeexclude from basecall within the period
1999437b43LEVEL 104/07/202201/07/2022 purgekeep in basecall within the period, most recent
1999437b43LEVEL 104/07/202201/05/2022 purgeexclude from basecall within the period
2992b6576LEVEL 111/07/2022  common callkeep in baseit was not found in the purges base, therefore it must be

 

Treated merged table

idphone_numberchanneldateinitial_datefinal_dateclassification
19992365bLEVEL 111/07/2022  common call
10993a9aaaLEVEL 101/08/202201/01/2020 purge
11993a9aaaLEVEL 101/08/202201/01/2020 purge
12993a9aaaLEVEL 103/10/202201/01/2020 purge
1399834528LEVEL 105/07/202201/07/2022 purge
1499834528LEVEL 105/07/202201/07/2022 purge
1599834528LEVEL 105/07/202201/07/2022 purge
1699988a65SAC21/10/202201/10/2022 purge
1799988a65SAC21/10/202201/10/2022 purge
1899988a65SAC21/10/202201/10/2022 purge
1999437b43LEVEL 104/07/202201/07/2022 purge
2992b6576LEVEL 111/07/2022  common call

 

I hope it helps you understand

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors