Text Before Delimiters

[Georgetimes]

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?

Customer	Desire result
Mike Smith	Mike Smith
Mike (aka John) Smith	Mike Smith
Mike (aka John Nick) Smith	Mike Smith
Mike aka John) Smith	Mike Smith
Mike aka John Smith) Smith	Mike Smith
Mike Smith aka John	Mike Smith
Mike A.K.A John) Smith	Mike Smith
Mike A.K.A John Nick) Smith	Mike Smith
Mike (Mik) (John) Smith (Johnson)	Mike Smith
(John Nick) Mike Smith (JJ Johnson)	Mike Smith

[lbendlin · Accepted solution]

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 · Accepted solution]

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.

[lbendlin · Accepted solution]

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]

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.