Extract text between delimiters, leave text when there are no delimiters

[Anonymous]

Hello,

Thing that needs to be solved- I do have flow of data that is coming every month to powerBi file.

As an example below- I want to extract text between delimiters "(" and ")", and leave just the name of employee.
Btw. there has always been a patern- 3 letters at the beginning, space and name in brackets "123 (Tomase)"

When I try to use split by delimiter functions in Power Query I can extract those names- "Bfe (Ben)", "Aaa (Anna)" etc.- that are in brackets, but when I do it- normal names dissapear -"Joshua", "Perry" etc.

Unfortunately I will not be able to get clean data- will have to either clean it by powerBi or excel.

Can it be done with built in functions?

The goal is to clean data- leave just the names that are in brackets and leave other values untouched.

[Anonymous]

@Jimmy801 

Thanks for your answer! 
Can U tell me where exactly and what should I paste ? When I try to do it in Advanced Editor, it always gives me an error.

After researching, still don't know what am I doing wrong.

Here is how it looks:

This is the code I see after entering Advanced Editor-without making any changes.

let
    Source = Excel.Workbook(File.Contents("C:\Users\-----------------.xlsx"), null, true),
    #"01.01.2021_Sheet" = Source{[Item="01.01.2021",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"01.01.2021_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "First"}})
in
    #"Renamed Columns"

Btw. This is the M Language- that is needed in Power Query? Wanna learn it

[Jimmy801 · Accepted solution]

Hello @Anonymous 

i tried to adapt your code. I cannot test it, so hopefully it will work

let
    Source = Excel.Workbook(File.Contents("C:\Users\-----------------.xlsx"), null, true),
    #"01.01.2021_Sheet" = Source{[Item="01.01.2021",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"01.01.2021_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "First"}}),
    GetFirstName = Table.TransformColumns
    (
        #"Renamed Columns",
        {
            {
                "First", 
                each if Text.Contains(_,"(") and Text.Contains(_,")") then Text.BetweenDelimiters(_, "(",")") else _
            }
        }
    )
in
    GetFirstName 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[edhans]

You paste that code into a blank query. See the steps below @Anonymous including the link that will take you to an article that will show you how to integrate @Jimmy801 's code into your queries.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Jimmy801]

Hello @Anonymous 

this is exactly why we have power quer 🙂

you can use Table.TranformColumns and apply there one function to the column "First" like this

{
    {
         "First", 
         each if Text.Contains(_,"(") and Text.Contains(_,")") then Text.BetweenDelimiters(_, "(",")") else _
    }
}

Here a complete example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckpLVdBwSs3TVIrVAfJS88C0Y2KigoZjXl4iRNgnsRjOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [First = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First", type text}}),
    GetFirstName = Table.TransformColumns
    (
        #"Changed Type",
        {
            {
                "First", 
                each if Text.Contains(_,"(") and Text.Contains(_,")") then Text.BetweenDelimiters(_, "(",")") else _
            }
        }
    )
in
    GetFirstName

that transforms this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy