Extract string without using delimiters in Power Query

[setis]

I have a column with Notes and I need to extract our Case ID. 

The format of the case ID is always 2 letters and 10 numbers.

As an example the format of one of the lines for this column can be:

"Awaiting amount for AU1548789545/ UDS 399. Email sent"

However, the format can change and order changes from line to line. 

Is it possible to find it and extract the ID to a new column?

[az38]

Hi @setis 

In Power Query Editor Mode, choose your column Notes, then try Transform - Extract - Text Before Delimiter and set delimiter as "/"

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[setis]

Hi @az38 , thanks for your answer. 

I can't always trust that there is a delimiter or the position of the ID in the string. 

[parry2k]

@setis does it always starts with AU and there is always / after case id number?

[setis]

Hi @parry2k ,

The ID's always start with 3 posible combinations. AU, EU, US.

[parry2k · Accepted solution]

@setis  create new blank query and then paste following script and you can see the steps which you can apply on your table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixPzCzJzEtXSMzNL80rUUjLL1JwDDU0NbEwt7A0NTHVVwh1CVYwtrTUU3DNTczMUShOzStRitXBrtMVSacCSTpDg8m1E6diIoyMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Pos", each (if Text.PositionOf([Col1],"AU")<>-1 then
Text.PositionOf([Col1],"AU") else
if Text.PositionOf([Col1],"US") <> -1 then 
Text.PositionOf([Col1],"US") else
Text.PositionOf([Col1],"EU")), Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Case Id", each if [Pos] <> -1 then
Text.Middle([Col1],[Pos],12) else null)
in
    #"Added Custom1"