ETL fixing Phone Numbers errors

[brunofrancesco]

Hi,

It`s a challenge for me to find a simple way to fix a input numbers.

In this scenario there are a lot wrong inputs the cases are :

(view: in the same column and different rows)

• Number with a lot of blank spaces between [like this: 55     777777  77]
• Number with "(  )" [for example: 55     (77)7777  77]  
• Anotations  [for example: 55     (77)7777  77  this is a number of my friend]  
• Old format for cellphones 
• Without Country Code and/or Without City Code
• Repeat Country Code, but, attention,  sometimes Country Code is the same of City Code 

the result expected is:

- 13  characters (only numbers) for cellphones without spaces

ex: CCcc9NNNNNNNN   (CC is Country Code, cc is city Code, 9 is a new add number for cellphone started, NNNNNNNN Number)

- 12  characters (only numbers) for Landline phone without spaces

Steps

1. Erase spaces, letters, special characters "( ) " 
2. LEN Fuction to Count Numbers if 13 and 12  - its ok, keep the number 
3. If 8 numbers of characters and starts with 9 or 8:  it need to add Country Code, City code and "9"  ex: CCcc9NNNNNNNN  - if no starts with 9 or 8 it need to add Country Code e City code ex: CCccNNNNNNNN 
4. If 9 numbers of characters:  it need to add Country Code e City code
5. If 10 numbers of characters and 3th number (cc"N"NNNNNNN) is 9 or 8 :  it need to add Country Code and 9 ex: CCcc9NNNNNNNN - if it is not 9 or 8 only add Country Code
6. If more than 13 characters cut to be 13 or 12 characters in the same rule of the step 3.

Somebody knows to coding this ? 

[Seward12533]

I’m sure it’s possible in M but you find it easier to do in combination with DAX using SWITCH(TRUE(), In a calculated column.

In M TRIM the date to remove any leading trailing blanks. Then remove the non essential characters- see this post https://community.powerbi.com/t5/Desktop/How-to-remove-nonessential-characters-such-as-punctuation/t...

Then in Calculted column something like this. I started to give you an idea but you will need to tweak the logic. The SWITCH TRUE will stop at first true and return it’s paired result.

Fixed Phone =
VAR LenP = LEN([phone])
VAR FirstC = LEFT([phone],2)
VAR 3RdC = MID([Phone],2,1) // might need to tweak
RETURN
SWITCH(True(), // Dax version of case statement
LenP=12||LenP=13,[phone],
LenP=8&&FirstC in {“9”,”8”}, [Country]&[City]&[Phone],
....
)

[brunofrancesco]

Thanks for the help!

I Write this Code:

Fixed Phone =
VAR phone = [customer_contact]
VAR LenContact = LEN(phone)
VAR Num8dig = LEFT(RIGHT(phone;8);1)
VAR CountryCode = 55
VAR CityCode = 22
RETURN
SWITCH(True();
LenContact<8;phone;
LenContact=8&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"}; CountryCode & CityCode & phone;
LenContact=8&&Num8dig in {"9";"8"}; CountryCode & CityCode &"9"&phone;
LenContact=9&&Num8dig in {"9";"8"}; CountryCode & CityCode & phone;
LenContact=10&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"};CountryCode & phone;
LenContact=10&&Num8dig in {"9";"8"}; CountryCode & LEFT(phone;2)&"9"& RIGHT(phone;8);
LenContact=12&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"}||LenContact=13;phone;
LenContact=12&&Num8dig in {"9";"8"}; LEFT(phone;4)&"9"& RIGHT(phone;8);
LenContact=13;phone;
LenContact>13&&Num8dig in {"9";"8"};RIGHT(phone;13);
LenContact>13&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"};RIGHT(phone;12)
;0)

but:

 "Expressions that generate variable data type can not be used to define calculated columns."

[Seward12533]

VAR is ok. But there must be something wrong with one of your statements. And sometimes is returning text other times numbers or something else like a Boolean. . You may need to use FORMAT to convert numbers to text or find other problem with the formula. Deconstruct and text your formula by commenting out your terms ( using // ) and introducing one at a time till you find it. Start with the Var statements for Cory and country code. They need to be inside quotes.

Fixed Phone =
VAR phone = [customer_contact]
VAR LenContact = LEN(phone)
VAR Num8dig = LEFT(RIGHT(phone;8);1)
VAR CountryCode = “55”
VAR CityCode = “22”
RETURN
SWITCH(True();
LenContact<8;phone;
// LenContact=8&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"}; CountryCode & CityCode & phone;
// LenContact=8&&Num8dig in {"9";"8"}; CountryCode & CityCode &"9"&phone;
// LenContact=9&&Num8dig in {"9";"8"}; CountryCode & CityCode & phone;
// LenContact=10&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"};CountryCode & phone;
// LenContact=10&&Num8dig in {"9";"8"}; CountryCode & LEFT(phone;2)&"9"& RIGHT(phone;8);
// LenContact=12&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"}||LenContact=13;phone;
// LenContact=12&&Num8dig in {"9";"8"}; LEFT(phone;4)&"9"& RIGHT(phone;8);
// LenContact=13;phone;
// LenContact>13&&Num8dig in {"9";"8"};RIGHT(phone;13);
// LenContact>13&&Num8dig in {"7";"6";"5";"4";"3";"2";"1";"0"};RIGHT(phone;12)
;0)