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 i...
Need Help
v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity 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.