Remove text between characters

[Nipius]

Hi!

I have a column in a Power BI query with names. In case of absence of a person, the system adds (On Leave) to the name of the employee. So, if John Doe is absent for 2 months, the report shows John Doe (On Leave).

Also, in some cases, the system provides an output where the name of the person is added in a different writing. Particularly, in Asian countries, the system adds the name of the person in the original writing.

In both case, I'd like to have everything between brackets (including the brackets) removed. Is there a solution to this? 

Thanks so much in advance for your support!

[Anonymous]

There is an even simpler solution.

You can create a new function called fun_ReplaceTextBetweenDelimiters, and in it add this code 👇

let
    fun_ReplaceTextBetweenDelimiters = (Text as text, StartDelimiter as text, EndDelimiter as text, optional ReplaceDelimiters as nullable logical, optional NewText as nullable text, optional TrimResult as nullable logical, optional FixDoubleSpaces as nullable logical) as text => 
        let
            // Add Default Parameters
            Default_ReplaceDelimiters = if ReplaceDelimiters is null then true else ReplaceDelimiters,
            Default_NewText = if NewText is null then "" else NewText,
            Default_TrimResult = if TrimResult is null then true else TrimResult,
            Default_FixDoubleSpaces = if FixDoubleSpaces is null then true else FixDoubleSpaces,
            //Do work
            TextBetweenDelimiters = Text.BetweenDelimiters(Text, StartDelimiter, EndDelimiter),
            TextToReplace = if Default_ReplaceDelimiters then Text.Combine({StartDelimiter,TextBetweenDelimiters,EndDelimiter}) else TextBetweenDelimiters,
            ReplacedText = Text.Replace(Text, TextToReplace, Default_NewText),
            //Clean Result
            TrimmedText = if Default_TrimResult then Text.Trim(ReplacedText) else ReplacedText,
            FixedSpaces = if Default_FixDoubleSpaces then Text.Replace(TrimmedText, "  ", " ") else TrimmedText
        in
            FixedSpaces
in
    fun_ReplaceTextBetweenDelimiters

Then, we can test it like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtAw1FTSAbGUYnWilVzd3BU0jEAiQBZYJCIyCqhGRwEsCOQoxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TestData = _t, TargetData = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"TestData", type text}, {"TargetData", type text}}),
    RunFunction = Table.AddColumn(ChangeType, "NewText", each fun_ReplaceTextBetweenDelimiters([TestData], "(", ")", true), type text),
    TestResult = Table.AddColumn(RunFunction, "Test", each [TargetData]=[NewText], type logical)
in
    TestResult

Input:

TestData	TargetData
ABC (1)	ABC
EFG (2)	EFG
XYZ (1, 2)	XYZ

Output:

TestData	TargetData	NewText	Test
ABC (1)	ABC	ABC	TRUE
EFG (2)	EFG	EFG	TRUE
XYZ (1, 2)	XYZ	XYZ	TRUE

[Anonymous]

Hi @Nipius ,

A little confuse with your requirments.

Do you want the output to look like this? or do you want to remove (on leave) in your report?

[Nipius]

@Anonymous Thanks for the response!

So, originally the data looks like this "Name (On Leave)" or "Name (Name in different writing)". I want everything to look like "Name". So for all the records where there is data between brackets in the records, I'd like to remove this part.

[Anonymous · Accepted solution]

Hi @Nipius ,

I hop[e you have access to edit queries.

if so these are the steps:

1-  Data looks like this

2- right click and hit on split column , you will get output like this. Hit ok

3. remove the column which was created after splitting.

4. Output looks like this

Let me know if this works.

Thanks,

Tejaswi