Hi Forum!
In my DB, I have, in the Start, the following situation :
- DATUM column: only dates (dd/mm/yyyy).
- Caller Number column: a list of all the phone numbers that called. Numbers can have different number of characters.
- Call Result Column: the result of the call which can be ANSWERED or NO ANSWERED.
| DATUM | Caller Number | Call Result |
|---|---|---|
| 02/01/2020 | 6283 | ANSWERED |
| 02/01/2020 | 22300200 | ANSWERED |
| 02/01/2020 | 22300200 | NO ANSWER |
| 02/01/2020 | 24417052 | NO ANSWER |
| 02/01/2020 | 25138553 | ANSWERED |
| 02/01/2020 | 460965082 | ANSWERED |
| 02/01/2020 | 460965082 | NO ANSWER |
In the Wanted tab I have the same list but with an extra column NOTANSWER_D which indicates 1 if the number that called on a certain day was not answered (NO ANSWER), in one or more attempts. Obviously, the same number can call several times a day and as soon as it has been answered (ANSWERED), the indication must be 0.
I reach the desired result with this formula in excel (→ I use a FR version of excel) :
=SI(ET([Call Result]="NO ANSWER";NB.SI.ENS([Caller Number];[@[Caller Number]]];[Call Result]; "ANSWERED";[DATUM];[@DATUM])=0);1;0)
In english ?
=IF(AND([Call Result]="NO ANSWER",COUNTIFS([Caller Number],[@[Caller Number]]],[Call Result], "ANSWERED",[DATUM],[@DATUM])=0),1,0)
| DATUM | NOTANSWER_D | Caller Number | Call Result |
|---|---|---|---|
| 02/01/2020 | 0 | 6283 | ANSWERED |
| 02/01/2020 | 0 | 22300200 | ANSWERED |
| 02/01/2020 | 0 | 22300200 | NO ANSWER |
| 02/01/2020 | 1 | 24417052 | NO ANSWER |
| 02/01/2020 | 0 | 25138553 | ANSWERED |
| 02/01/2020 | 0 | 460965082 | ANSWERED |
| 02/01/2020 | 0 | 460965082 | NO ANSWER |
| 02/01/2020 | =SI(ET([Call Result]="NO ANSWER";NB.SI.ENS([Caller Number];[@[Caller Number]];[Call Result];"ANSWERED";[DATUM];[@DATUM])=0);1;0) | 460965082 | NO ANSWER |
How can I transform this formula containing a COUNTIFS (NB.SI.ENS) into an M formula to get this result directly in Power Query?
Thanks,
Hi Lionel,
I wasn't sure if you wanted the results grouped by caller number or not. The example result in the post (above) isn't the same as the example result table in the workbook.
So I wrote 2 queries, 1 grouping the caller number and 1 that doesn't.
The Not Grouped query uses a Custom Function to check the calls each day, by caller number, and calculates if the number was answered at all.
See attached.
Regards
Phil
Hi Philip,
I am sorry for the error in the post board. (⊙﹏⊙)
Indeed, my goal is to keep all the lines as I am doing other calculations.
Both proposals are very interesting and I will study them in depth to understand all the steps.
Again, many thanks,
BR,
Lionel
You're welcome.
