Extracting unique ID's from a text string

[nullpowerbi]

Hi,

Im struggling to extract unique references from a longer string of text, which relates to a comment manually entered to a system. 

The unique ID's can appear anywhere in a comment box as they are manually entered by users of the system. examples below;

Hello, blah blah blah DL123123

Hello, UL123123, blah blah blah

ul123123, blah blah blah, hello.

Hello, DL 123123, blah

UL & DR are always followed by digits but can vary in consistency. My aim is to pull these references into a new custom column in the fewest steps.

Regards

[edhans · Accepted solution]

The following will do this in 3 columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Replaced Comma with Space" = Table.ReplaceValue(#"Changed Type",","," ",Replacer.ReplaceText,{"Data"}),
    #"Added DL or UL Location" = Table.AddColumn(#"Replaced Comma with Space", "DL or UL Location", each if Text.PositionOf([Data],"DL", 1, Comparer.OrdinalIgnoreCase) > -1 then 
Text.PositionOf([Data],"DL", 1, Comparer.OrdinalIgnoreCase) else
Text.PositionOf([Data],"UL", 1, Comparer.OrdinalIgnoreCase), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added DL or UL Location", "Case Number", each Text.Middle([Data],[DL or UL Location],9), type text),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Case Number", Text.Trim, type text}})
in
    #"Trimmed Text"

But that is for readability. This will do it in one.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
    #"Replaced Comma with Space" = Table.ReplaceValue(#"Changed Type",","," ",Replacer.ReplaceText,{"Data"}),
    #"Added DL or UL Location" = Table.AddColumn(#"Replaced Comma with Space", "DL or UL Location", each Text.Trim(
    Text.Middle([Data],
        if Text.PositionOf([Data],"DL", 1, Comparer.OrdinalIgnoreCase) > -1 then 
            Text.PositionOf([Data],"DL", 1, Comparer.OrdinalIgnoreCase) else
            Text.PositionOf([Data],"UL", 1, Comparer.OrdinalIgnoreCase)
        ,9)
        ), type text)
in
    #"Added DL or UL Location"

The first thing I did was replace commas with spaces to eliminate different delimiters. You might need to add colons, semi-colons, periods, etc. Then I found DL or UL regardless of case, then pulled 9 chars (2 letters, 6 numbers, and allowed for a space between), then trimmed it.

I can envision you'll need add additional logic though because users will be creative in messing with you. DL-123456 for example. Replace - with space then as well. 

I did this in Excel, so you cannot just drop that M code in Power BI without changing your source table reference.