Forum Discussion

brunofrancesco's avatar
brunofrancesco
Frequent Visitor
8 years ago

ETL fixing Phone Numbers errors

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 ? 

 

 

 

Need Help

3 Replies

  • Seward12533's avatar
    Seward12533
    Solution Sage
    8 years ago
    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/td-p/143020

    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's avatar
      brunofrancesco
      Frequent Visitor
      7 years ago

      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's avatar
        Seward12533
        Solution Sage
        7 years ago
        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)