Replace Invalid Values in a List

[JBelcher]

I have a column with a long list of values.  There are only a 7 valid values, but due to typos there are dozens of values in that list.  I'd like to make it so that only the 7 valid values are kept in the list, and all other values are replaced with "Invalid".   I can't seem to find an easy way to do this.  The best I've come up with is to make a new column and use Switch to "switch" valid values with valid values, and change everything else to "Invalid", but I'd really like to just fix the original column.

[v-yanjiang-msft]

Hi @JBelcher ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @JBelcher ,

Has your problem been solved by @tackytechtom's suggestion? You can also use Table.TransformColumns function. 

I create a sample.

If you want to replace the rows starts with 0 to Invalid, you can directly add a new line in the advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMjACU8YQjolSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    New=Table.TransformColumns(Source,{"Column1",each if Text.StartsWith(_,"0")then"Invalid"else _})
in
    New

 Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tackytechtom]

Hi @JBelcher ,

Does this one help you here?

Let me know! 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/