Forum Discussion

JBelcher's avatar
JBelcher
New Member
4 years ago

Replace Invalid Values in a List

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.

Need Help

3 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.

     

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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