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 ...
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
Vijay_A_Verma
Most Valuable Professional
Most Valuable ProfessionalInsert 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
Thanks.