Forum Discussion
Conditionally replace only first character
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
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
That first bit did it---thank you do much!
Thanks.
