Forum Discussion
Conditionally replace only first character
I have ID strings in a certain column. The first character of some of these strings should be a zero but instead have a capital O.
When the first character is a capital O, I need to replace just that first character with a zero. Any other O characters in the remainder of the string should not be changed.
How do I do this in Power Query?
Insert this step where you will need to change #"Previous Step" with your previous step and Data with your column name
= Table.ReplaceValue(#"Previous Step",each [Data],each if Text.Start([Data],1)="O" then Text.ReplaceRange([Data],0,1,"0") else [Data], Replacer.ReplaceValue,{"Data"})See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck/NL0pPVdJRMjA0Mk5JS1eK1YlWCs7NLMkAivkXlVQagkV8MosTgQL5ZqZAmAoW8s3MBunzryxVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Data = _t]), Custom1 = Table.ReplaceValue(Source,each [Data],each if Text.Start([Data],1)="O" then Text.ReplaceRange([Data],0,1,"0") else [Data], Replacer.ReplaceValue,{"Data"}) in Custom1
3 Replies
Insert this step where you will need to change #"Previous Step" with your previous step and Data with your column name
= Table.ReplaceValue(#"Previous Step",each [Data],each if Text.Start([Data],1)="O" then Text.ReplaceRange([Data],0,1,"0") else [Data], Replacer.ReplaceValue,{"Data"})See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck/NL0pPVdJRMjA0Mk5JS1eK1YlWCs7NLMkAivkXlVQagkV8MosTgQL5ZqZAmAoW8s3MBunzryxVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Data = _t]), Custom1 = Table.ReplaceValue(Source,each [Data],each if Text.Start([Data],1)="O" then Text.ReplaceRange([Data],0,1,"0") else [Data], Replacer.ReplaceValue,{"Data"}) in Custom1Thanks.
