Forum Discussion

ashuaswinireddy's avatar
ashuaswinireddy
Icon for Helper I rankHelper I
3 years ago
Solved

How to extract name from text column

Hello All,

 

I have below column and would like to extract only names from below column. Highlighted names in bold in below example. Can you please let me know how to do that in power query. 

 

Current Data:

Column 1
177: Enter : Anusha Reddy (Parent: 176);178: Energy : Doug Parks (Parent: 176);179: Architecture: Aswini Siddareddy (Parent: 176);180: Mobility: Maria Whitmer, Frank Bruse (Parent: 176)
181: Drive : Cathy Parks (Parent: 176);182: Architecture: Asha Patel (Parent: 176);183: Mobility: Maria Carl, Frank Ply (Parent: 176)
176: Data : Nirav Mehta (Parent 178); 177: Hive : Megan Ben (Parent 178)
 

 

Expected Result:

Column 1
Anusha Reddy ;Doug Parks ;Aswini Siddareddy ; Maria Whitmer, Frank Bruse 
Cathy Parks ; Asha Patel ;Maria Carl, Frank Ply 
 Nirav Mehta ; Megan Ben 
 

 

Thank you!

 

Regards,

Ashwini

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • CNENFRNL's avatar
    CNENFRNL
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBNi8IwEIDhvzL0tIIHu4KJ9bR+4aVL0cMepIfRDu1gjTBNXfLvHb8O0t4S8mZ4kv0+io1JYOU8CSTw49qmQthSUQT4ylDI+QRiMxnMYmPvHUkZNFxe2hL0/NR0sqmOkWPFno6+FdJd88+OYcdFoWXPZDtKIL0cuGYfdIXCCH864EwyhLWgO8Fc2oY+70X5UPU2VozwlRS1QF+FfpX97qr0oRl6qjvtuMtZoNRvS1aHPomZqAQ9KuSXBa+QUqW7V6mhHczg8dubJzelEh3MyX00UZ7fAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Txt = _t]),
    Extracted = Table.TransformColumns(Source, {"Txt", each Text.Combine(List.Transform(Text.Split(_,";"), each Text.BetweenDelimiters(_,": "," (",1)),"; ")})
in
    Extracted

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBNi8IwEIDhvzL0tIIHu4KJ9bR+4aVL0cMepIfRDu1gjTBNXfLvHb8O0t4S8mZ4kv0+io1JYOU8CSTw49qmQthSUQT4ylDI+QRiMxnMYmPvHUkZNFxe2hL0/NR0sqmOkWPFno6+FdJd88+OYcdFoWXPZDtKIL0cuGYfdIXCCH864EwyhLWgO8Fc2oY+70X5UPU2VozwlRS1QF+FfpX97qr0oRl6qjvtuMtZoNRvS1aHPomZqAQ9KuSXBa+QUqW7V6mhHczg8dubJzelEh3MyX00UZ7fAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Txt = _t]),
    Extracted = Table.TransformColumns(Source, {"Txt", each Text.Combine(List.Transform(Text.Split(_,";"), each Text.BetweenDelimiters(_,": "," (",1)),"; ")})
in
    Extracted