Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Extract specific data from inconsistent string into a new column

Hi all   I need to extract some specific text from a string to be used as a future reference, problem is due the received data not being very consistant, I've tried using the extract funtion in pow...
Need Help
ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

From your example, it appears that the pattern you are looking for is a substring that

  • starts with "br"
  • followed by nothing or a non-digit non-letter character
  • followed by one or more digits

That being the case, you could either use Regular Expressions (using Python, R or Java in Power BI/Power Query)

or try the following which uses native M functions to match that pattern.

  • Split after the "br"
  • Then split that by the transition from a digit to a non-digit

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JYwxDsIwDAC/YmWmEqgUXsDGBGOUwcUuMpQYHEeivwfCeHfSxRiOjHe8MkxaM4FkuKlkD2kVw8FMDS5KDKNBvx6aPS/F+QHc4mib/q8nrLN/udvud0BSnjMuTC2d+FXFfhMUAn47ZxfNJaT0AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Problem Description" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Problem Description", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Extract", each 
        let 
            after= Splitter.SplitTextByCharacterTransition({"0".."9"},(c)=>not List.Contains({"0".."9"},c))
                (Text.AfterDelimiter([Problem Description],"br")){0}
        in 
            if List.Contains({"0".."9"}, Text.End(after,1)) 
                then "br" & after 
                else null, 
                type nullable text)
in
    #"Added Custom"