cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nullpowerbi Frequent Visitor
Frequent Visitor

Extracting unique ID's from a text string

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
edhans Super Contributor
Super Contributor

Re: Extracting unique ID's from a text string

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.

View solution in original post

1 REPLY 1
edhans Super Contributor
Super Contributor

Re: Extracting unique ID's from a text string

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.

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 282 members 3,085 guests
Please welcome our newest community members: