Forum Discussion
Replace spaces with an asterisk in Power Query
Hello,
I am looking to find a way to find and replace spaces in a cell with an asterisk so that I can run the rest of the Power Query steps of my template. From the below image I am looking to add a step where if there is a "Remark=" text in the Remarks 10 column it would replace the spaces in between the text/numbers to asterisks as noted in the Expected Result column. I was looking at the Find and Replace function but since the Remarks 10 column can include other descriptions it is not going to work well. Thanks!
You can use the Table.ReplaceValue function. Replace #"Previous Step" with the Obvious
#"Remark Asterisk"=Table.ReplaceValue( #"Previous Step", each [Remarks 10], null, (x,y,z) as text=> if Text.StartsWith(y,"REMARK=") then Text.Replace(x," ","*") else x, {"Remarks 10"})
3 Replies
You can use the Table.ReplaceValue function. Replace #"Previous Step" with the Obvious
#"Remark Asterisk"=Table.ReplaceValue( #"Previous Step", each [Remarks 10], null, (x,y,z) as text=> if Text.StartsWith(y,"REMARK=") then Text.Replace(x," ","*") else x, {"Remarks 10"})Thank you this worked perfectly!
You should be able to use the Power Query step of "Replace Values..." if you right click on the column.
You can't see it, but there is a space character in the Value to find field above.
Result:
