I'm using Text.Contains and need to remove a single text value and add another value in its place.

[apeeltyler]

Below is the formula I'm using

if Text.Contains([Employee ID], "T") then "0" else [Employee ID]

What I'm after is if the Employee ID contains a 'T', for example: T0123, I want to remove the 'T' and add a '0' (zero) in place of the 'T'.

How do I accomplish this?

[Syk · Accepted solution]

If your ID's are a specific length, you can use text.end 
try

if Text.Contains([Employee ID], "T") then "0" & text.right(Employee ID,4) else [Employee ID]

[apeeltyler]

Thanks!  However, I'm getting this error:

Expression.Error: The name 'Text.Right' wasn't recognized. Make sure it's spelled correctly.

edit to add:

If it helps, I'm in Power Query Editor creating a new column 

= Table.AddColumn(#"Removed Columns2", "EE ID", each if Text.Contains([Employee ID], "T") then "0" & Text.Right([Employee ID],4) else [Employee ID])

[Syk]

My bad! It's text.end 🙂

[apeeltyler]

It's funny because that "worked", no Syntax Errors were detected, but all results came back as Error.  Employee ID is a Whole Number column

[Syk]

Hmm.. It shouldn't automatically set it to whole number if some of them start with 'T'... Try to add a change type right before you add this column. Set it to a text type instead.

[apeeltyler]

Your formula worked!  What's odd is that the folks that have 'T' in the ID pulled in as Error.  There were only 3, so I'm just removing them, as they were going to be filtered out.  

I'm definately keeping your formula around for future use. Thanks!!🍻