Multiple Table.ReplaceValue

[CountryBoy_71]

Good afternoon all,

Wondering if someone can help explain why this isn't working? The first example is based on another post here, the second is what I am attempting. For me, the "[ ]" resulted in a syntax error, while wrapping in the "{ }" provided valid code but doesn't actually do anything.

= Table.ReplaceValue(#"Changed Type",each [Service Number], each if [Service Number] = " " then "" else if [Service Number] = "-" then "" else [Service Number],Replacer.ReplaceText,{"Service Number"})

= Table.ReplaceValue(#"Changed Type",each {"Service Number"}, each if {"Service Number"} = " " then "" else if {"Service Number"} = "-" then "" else {"Service Number"},Replacer.ReplaceText,{"Service Number"})

The column is a 'text' type and then converted to a 'int' in the next step.

I also realize that trying to make one step out of two may be a bit of a waste, but I also have another set of 4, replacing values that I am trying to clean up.

 

[JGordon11]

try

= Table.TransformColumns(#"Changed Type",{"Service Number", each if List.Contains({" ", "-"},_) then null else _})

 

[GraH]

try

= Table.TransformColumns(#"Changed Type",{"Service Number", each if List.Contains({" ", "-"},_) then null else _})

This would never cross my mind. Cool tip!
I always went with something like below.

= Table.ReplaceValue(#"Changed Type",each [Service Number], each if List.AnyTrue({[Service Number] = " " , [Service Number] = "-"}) then "" else [Service Number],Replacer.ReplaceText,{"Service Number"})

 

[RoryA]

For me, the "[ ]" resulted in a syntax error

The first syntax is correct assuming those are the correct step and column names. What was the actual error message?

 

[CountryBoy_71]

The first syntax is correct assuming those are the correct step and column names. What was the actual error message?

The 'error' was actually just a result of my fat finger...got that resolved, now, all the of the above solutions return valid syntax for me except that none of them actually return results. I am still left with the same column of data. The subsequent step changed type to 'int' and that was error'ing out because the replace step didn't actually replace anything.

I feel like some days excel and I aren't very good friends.

 

[RoryA]

If it's not replacing anything, then it must be that none of the service number data actually matches your tests.

 

[GraH]

So if the service number needs to be a number, then you can changes the type, then from the UI replace error. It's in the same place Here you found replace values.

 

[JEC]

Here one more

=List.Accumulate({" ","-"}, #"Changed Type", (s,c) => Table.ReplaceValue(s,c,null,Replacer.ReplaceValue,{"Service Number"}))

 

[CountryBoy_71]

Here one more

=List.Accumulate({" ","-"}, #"Changed Type", (s,c) => Table.ReplaceValue(s,c,null,Replacer.ReplaceValue,{"Service Number"}))

Thanks @JEC This is essentially the one that I went with. Only difference is that I created a separate referencing list. Overkill for two items but practice!