How to extracring only alphanumaric values in powerbi

user_15864084 0
Viewed 24

Data ABC123 JKL685 UJKLPN 655789

Output: ABC123 and JKL685 showing true and remaining are false. How to achieve this scenarios.

powerbi-desktop
1 Answers

Here is my suggestion using Power Query:

let
    numbers = List.Transform(List.Numbers(0,10), each Text.From(_) ),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVorViVby8vYxszAFM0OB7AA/MNPM1NTcwlIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Filtered numerical" = Table.SelectRows(Source, each try if Value.Type(Number.From([Data])) = type number then false else true otherwise true),
    #"Filtered alphabetical" = Table.SelectRows(#"Filtered numerical" ,
        (FindStrings) => 

    List.AnyTrue(List.Transform(numbers, each Text.Contains((FindStrings[Data]),_ ))))
    
    
in
    #"Filtered alphabetical"

It first filters away the fully numerical entries by attempting to convert to number and keeping only rows that cannot be converted. It then filters rows that only contain letters. It's probably not extremely efficient, but works with the sample data.

user_165280000
Related