Forum Discussion
Solved
Extract string without using delimiters in Power Query
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?
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"
