Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.