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 ...
Need Help
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)