I have a text column in PQ that contains both numbers and alpha-numerics. I would like to be able to filter out the true numbers leaving the records that have the alpha-numeric values. Has anyone run into this challenge before? Any suggestions would be most appreciated.
Thanks S. Smyth
Hi Sonny,
The code below only removes letters from the string so you're left with numbers. Note: It doesn't remove special characters, and it assumes your data is in a table, but obviously you'd need to modify the code if your data is in a record or list.
let
Source =
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYwxCsAwDAP/kkfE6tixc8hQQgaBiR/g/0OjBoyMJZ3HKMsID14IYzokFhJ64D+q/J0inYsJq3u2t6gdOICemIpiKnFI3P3pbytzfg==", BinaryEncoding.Base64), Compression.Deflate)),
let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]),
Res = Text.Combine(List.RemoveItems(List.RemoveItems(Text.ToList(Source[String]{0}),{"a" .. "z"}),{"A" .."Z"}),"")
in
Res
If you get stuck, please provide a sample file and we can help you further.
Mynda
Mynda,
Thank you for your response. Actually I have a different scenario. My data is in a table and I would like to filter out all the rows that aren't numbers. Example 789 vs D0001. See Before and After below.
Before
123
456
D0001
CAT/ 499
789
After
123
456
789
Thank you,
Sonny
Hi Sonny,
Oh, ok. That's easy. Change the data type for the column to whole or decimal number. Those values with text will return errors. You can simply filter out the errors.
Mynda
