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.
My overall goal is to have a map showing where calls are coming from by area code. I'm torn on what to do with the bad data....default it to the most common area code (913), leave it blank, mark it undefined in some way. I'm open to feedback there.
Now, on to the data. I want a separate column that just has the area code of the phone number. The most common form of the phone number is 11 digits long starting with 1 followed by the 3 digit area code (ex: 15672347890). I can easily get that.
Here are my exceptions (numbers changed to 2345678 where privacy is a concern). Should I do a nested IF statement to address all of these? What would that look like if we defaulted all unobtainable area codes to '913'?
+2542345678
+8132345678
10000000000
Unavailable
110000000
162017
1.19131E12
1.44291E12
+12012345678
919442345678
+114022345678
15712345678;verstat=TN
anonymous@199.199.199.199
NONE@199.199.199.199
+1217234567890
Solved! Go to Solution.
Here's what I ended up using. It doesn't address all of my exceptions but gets me in the ballpark. Specifically, I'm excluding any numbers under 10 digits, any non numeric values and any result that starts with a 1.
CallingAreaCode =
Var ACode=left(right(Call_Log[CallingNumber],10),3)
Return
IF(iserror(value(ACode)),"UNK"
,if(value(Acode)<200,"UNK"
,if(len(Call_Log[CallingNumber])<=9, "UNK"
,ACode)))
The values on here that have an area code but that I'm not capturing (or capturing incorrectly) are:
1.19131E12
1.44291E12
15712345678;verstat=TN
+1217234567890
I could probably write a few more IF statements to look at the first 5 or so characters and grab the remaining exceptions but that seems like more trouble than it's worth.
Here's what I ended up using. It doesn't address all of my exceptions but gets me in the ballpark. Specifically, I'm excluding any numbers under 10 digits, any non numeric values and any result that starts with a 1.
CallingAreaCode =
Var ACode=left(right(Call_Log[CallingNumber],10),3)
Return
IF(iserror(value(ACode)),"UNK"
,if(value(Acode)<200,"UNK"
,if(len(Call_Log[CallingNumber])<=9, "UNK"
,ACode)))
The values on here that have an area code but that I'm not capturing (or capturing incorrectly) are:
1.19131E12
1.44291E12
15712345678;verstat=TN
+1217234567890
I could probably write a few more IF statements to look at the first 5 or so characters and grab the remaining exceptions but that seems like more trouble than it's worth.
Hi @OPJayhawk ,
Here are the steps you can follow:
1. Create calculated column.
Flag1 =
IF(
LEN('Table'[Flag])=11&&MID('Table'[Flag],1,1)="1",RIGHT('Table'[Flag],3),BLANK())
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks for the reply and the attachment.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |