Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
Thanks.
That first bit did it---thank you do much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.