Forum Discussion

Georgetimes's avatar
Georgetimes
Frequent Visitor
2 years ago
Solved

Text Before Delimiters

Hi Everyone,

 

Need your help with the below. I'm trying to delimit the first name and Surname, however the text comes in different formats. Please find below all the format types and the result I'm trying to achieve.

 

Please bear in mind each row will normally show a different First name / Surname, however I've left it the same just to keep it simple.

 

Any idea how I can write this in PowerQuery and create a new column with the desire result?

 

CustomerDesire result
Mike SmithMike Smith
Mike (aka John) SmithMike Smith
Mike (aka John Nick) SmithMike Smith
Mike aka John) SmithMike Smith
Mike aka John Smith) SmithMike Smith
Mike Smith aka JohnMike Smith
Mike A.K.A John) SmithMike Smith
Mike A.K.A John Nick) SmithMike Smith
Mike (Mik) (John) Smith (Johnson)Mike Smith
(John Nick) Mike Smith (JJ Johnson)Mike Smith
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago
    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W8s3MTlUIzs0syVCK1YFyNRKzExW88jPyNHHKKPhlJmdjSOPUB9cGFseQBnPhihDijnreeo7YTURIYXeKBpDUVNBA0gvhFOfnaYJVaSDpRXKEhpeXAkJdLAA=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Customer = _t]
  ), 
  #"Added Custom1" = Table.AddColumn(
    Source, 
    "Desire result", 
    each 
      let
        l = List.Select(
          Text.Split(Text.Replace([Customer], "aka ", "_"), " "), 
          each not Text.Contains(_, "(") and not Text.Contains(_, ")") and not Text.Contains(_, "_")
        )
      in
        List.First(l) & " " & List.Last(l), 
    type text
  )
in
  #"Added Custom1"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

    Thanks for the solution lbendlin  provided, and i want to offer some more information for user to refer to.

    hello Georgetimes , you can create a custom column first and name it custom

    Text.Length([Customer])

    Then based on the custom column then crete a custom column.

    let a=List.Min(#"Added Custom"[Custom]),
b=Table.SelectRows(#"Added Custom", each [Custom]=a)[Customer]{0},
c=Text.Split(b," "),
d=Text.Split([Customer]," ")
in Text.Combine(List.Intersect({d,c})," ")

     

    Output

     

    And you can refer to the attachement.

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45W8s3MTlUIzs0syVCK1YFyNRKzExW88jPyNHHKKPhlJmdjSOPUB9cGFseQBnPhihDijnreeo7YTURIYXeKBpDUVNBA0gvhFOfnaYJVaSDpRXKEhpeXAkJdLAA=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Customer = _t]
  ), 
  #"Added Custom1" = Table.AddColumn(
    Source, 
    "Desire result", 
    each 
      let
        l = List.Select(
          Text.Split(Text.Replace([Customer], "aka ", "_"), " "), 
          each not Text.Contains(_, "(") and not Text.Contains(_, ")") and not Text.Contains(_, "_")
        )
      in
        List.First(l) & " " & List.Last(l), 
    type text
  )
in
  #"Added Custom1"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

    • Georgetimes's avatar
      Georgetimes
      Frequent Visitor
      2 years ago

      Thank you for your reply.

       

      I'm having issues with changing the Source. Are you able to help with this, please?

      The table is called "Table1" and has 3 columns (would like to keep them as well). Please see below how the columns would look like before modifying them.

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    Thanks for the solution lbendlin  provided, and i want to offer some more information for user to refer to.

    hello Georgetimes , you can create a custom column first and name it custom

    Text.Length([Customer])

    Then based on the custom column then crete a custom column.

    let a=List.Min(#"Added Custom"[Custom]),
b=Table.SelectRows(#"Added Custom", each [Custom]=a)[Customer]{0},
c=Text.Split(b," "),
d=Text.Split([Customer]," ")
in Text.Combine(List.Intersect({d,c})," ")

     

    Output

     

    And you can refer to the attachement.

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.