Replace spaces with an asterisk in Power Query

[Bleucherie]

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!

[ronrsnfld · Accepted solution]

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"})

[Bleucherie]

Thank you this worked perfectly!

[ToddChitt]

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: