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 power query but the result is still too varied as the below suggests.

 

 

Extract after gives to much info behind the delimiter, which is br.

Extract between 'br' and ' ' (space) or 2nd ' ' (space) also provides unnecessary results but includes some ommited results.

 

Ideally I would need either a DAX formula or other solution to get to either the expected result or ideal result shown in the example... problem is I don't even know where to start with this.

 

Any assistance would be greatly appreciated.

Need Help

2 Replies

  • 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"