Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
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
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.
Hi @JBelcher ,
Does this one help you here?
Let me know! 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |