Power Query - Cleaning start of phone numbers

[Anonymous]

Hi all,

I'm currently doing some analysis on the cost of phone numbers we call, but they aren't the cleanest and I need to remove some specifics from the front of the number in Power Query e.g. sometimes I have "9*007" or "907" at the start of a mobile number, which needs changing to "07". How can I replace values in power query, using a left formula as I have done below in DAX?

Number = IF(LEFT('Outbound Calls'[PhoneNumber],4)="9*00",MID('Outbound Calls'[PhoneNumber],4,LEN('Outbound Calls'[PhoneNumber])-3)

,IF(LEFT('Outbound Calls'[PhoneNumber],3)="9**",MID('Outbound Calls'[PhoneNumber],4,LEN('Outbound Calls'[PhoneNumber])-3)

, IF(LEFT('Outbound Calls'[PhoneNumber],3)="900",MID('Outbound Calls'[PhoneNumber],3,LEN('Outbound Calls'[PhoneNumber])-2)

,IF(LEFT('Outbound Calls'[PhoneNumber],2)="9*",MID('Outbound Calls'[PhoneNumber],3,LEN('Outbound Calls'[PhoneNumber])-2)

, IF(LEFT('Outbound Calls'[PhoneNumber],1)="9",MID('Outbound Calls'[PhoneNumber],2,LEN('Outbound Calls'[PhoneNumber])-1),'Outbound Calls'[PhoneNumber])))))

Kind regards,

Jordan 

[PhilipTreacy]

Hi @Anonymous 

Download sample PBIX file with data and code

Try this, it works on the sample data I created in that PBIX file

= Table.AddColumn(Source, "Custom", each Text.Replace(Text.Replace("*"&Text.Replace("*"&[Phone Num],"*9", ""),"*00","0"),"*",""))

Regards

Phil

[themistoklis · Accepted solution]

@Anonymous 

Τhe power query query formula should look like this 

if Text.Start('Outbound Calls'[PhoneNumber], 4) = "9*00"

then Text.Middle('Outbound Calls'[PhoneNumber], 4, Text.Length('Outbound Calls'[PhoneNumber])-3)

else if Text.Start('Outbound Calls'[PhoneNumber], 3) = "9**" 

then Text.Middle('Outbound Calls'[PhoneNumber], 4, Text.Length('Outbound Calls'[PhoneNumber])-3)

else if Text.Start('Outbound Calls'[PhoneNumber], 3) = "900" 

then Text.Middle('Outbound Calls'[PhoneNumber], 3, Text.Length('Outbound Calls'[PhoneNumber])-2)

else if Text.Start('Outbound Calls'[PhoneNumber], 2) = "9*" 

then Text.Middle('Outbound Calls'[PhoneNumber], 3, Text.Length('Outbound Calls'[PhoneNumber])-2)

else if Text.Start('Outbound Calls'[PhoneNumber], 1) = "9" 

then Text.Middle('Outbound Calls'[PhoneNumber], 2, Text.Length('Outbound Calls'[PhoneNumber])-1)

else ""