Forum Discussion

PowerBINoob24's avatar
PowerBINoob24
Resolver I
3 years ago
Solved

Stripping characters from string

I have a number of records that have an employee's title and name.  The format is Title (Name).  Is there a way to strip all characters away except for the employee's name?   Thanks in advance.
Need Help
  • AbhinavJoshi's avatar
    AbhinavJoshi
    3 years ago

    Thanks for clarifying. You can use Extract function in Power Query Editor. Please find the code and sceenshot attached.

     


     #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Changed Type1", {{"Name", each Text.BetweenDelimiters(_, "(", ")"), type text}})

  • PowerBINoob24's avatar
    PowerBINoob24
    3 years ago

    So easy once you know where to look.  Thanks!

AbhinavJoshi's avatar
AbhinavJoshi
Responsive Resident
3 years ago

Hello PowerBINoob24. You can achieve this in Power Query Edior using split column by delimiter. First I split it using the "(", and then I do it replace values where I replace ")" with blank to get the name only. Please find the full source code ,and screenshots attached.

 

 

 

 


let
Source = Excel.Workbook(File.Contents("C:\Users\AJoshi\Downloads\Random2.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Name", Splitter.SplitTextByDelimiter("(", QuoteStyle.None), {"Name.1", "Name.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2",")","",Replacer.ReplaceText,{"Name.2"})
in
#"Replaced Value"

 

I hope it helps!

  • PowerBINoob24's avatar
    PowerBINoob24
    Resolver I
    3 years ago

    Thank you. While that works, I'm not looking to split the columns.  Just need to strip everything but the name.

    • AbhinavJoshi's avatar
      AbhinavJoshi
      Responsive Resident
      3 years ago

      Thanks for clarifying. You can use Extract function in Power Query Editor. Please find the code and sceenshot attached.

       


       #"Extracted Text Between Delimiters" = Table.TransformColumns(#"Changed Type1", {{"Name", each Text.BetweenDelimiters(_, "(", ")"), type text}})

      • PowerBINoob24's avatar
        PowerBINoob24
        Resolver I
        3 years ago

        So easy once you know where to look.  Thanks!