Forum Discussion

setis's avatar
setis
Post Partisan
6 years ago
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 b...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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"

     

parry2k's avatar
parry2k
Super User
6 years ago

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

setis's avatar
setis
Post Partisan
6 years ago

Hi parry2k ,

 

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

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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"