Forum Discussion
I'm using Text.Contains and need to remove a single text value and add another value in its place.
If your ID's are a specific length, you can use text.end
tryif Text.Contains([Employee ID], "T") then "0" & text.right(Employee ID,4) else [Employee ID]
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])
Resident RockstarMy bad! It's text.end 🙂
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.
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!!🍻