Remove leading 1 (country code) in phone number in PQ when it appears

[samdep]

Hi All,

I was wondering if someone could help me with the code in Power Query (using the trim function?) to remove the leading 1 (or country code) from phone numbers... It's present in some records, but not all, so I'm in need of some sort or leftstrip type of conditional statement -- as the presence of the 1 is impacting my join/merge. 

Thanks in advance! 

[watkinnc · Accepted solution]

Try this:

RemoveOne = Table.AddColumn(PreviousStepName, "Truncated", each if Text.StartsWith([PhoneNumberColumn], "1") then Text.RemoveRange([PhoneNumberColumn], 0) else [PhoneNumberColumn])

--Nate

[watkinnc · Accepted solution]

No need for the parentheses after the "if" in this case-you can remove them. But you DO need a final "else" clause:

each if Text.StartsWith([Client_Number__c],"1") then Text.RemoveRange([Client_Number__c],0) else if
[Client_Number__c] = "" then ""

else [Client_Number_c]

--Nate

[watkinnc · Accepted solution]

Try this:

RemoveOne = Table.AddColumn(PreviousStepName, "Truncated", each if Text.StartsWith([PhoneNumberColumn], "1") then Text.RemoveRange([PhoneNumberColumn], 0) else [PhoneNumberColumn])

--Nate

[samdep]

@watkinnc thanks so much, this worked perfectly! One follow-up, I am getting an error for fields where the phone number is blank in my new conditional column, so I assume a nested if/then should solve for that --

each if Text.StartsWith([Client_Number__c],"1") then Text.RemoveRange([Client_Number__c],0) else if(
[Client_Number__c] = "" then "")  

The second 'then' seems to be producing an error - but the error is 'Token RightParen expected', so I'm not sure what the issue is. Appreciate any feedback you/anyone may have. thx!

[watkinnc · Accepted solution]

your final conditional has to be else, not else if.

--Nate

[watkinnc · Accepted solution]

No need for the parentheses after the "if" in this case-you can remove them. But you DO need a final "else" clause:

each if Text.StartsWith([Client_Number__c],"1") then Text.RemoveRange([Client_Number__c],0) else if
[Client_Number__c] = "" then ""

else [Client_Number_c]

--Nate

[V-pazhen-msft]

@samdep 
Have you solved? If not, you might want to share a sample data and expected output.

Paul Zheng _ Community Support Team

[AlB]

Hi @samdep 

Do you want to show some examples to clarify when you want to remove the 1 and when not?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.