On the Transform tab, Extract, Text before delimiter. Enter " (" i.e. space and left parenthesis without the quotation marks as the delimiter.

This will generate a code like this:

= Table.TransformColumns(Source, {{"Column1", each Text.BeforeDelimiter(_, " ("), type text}})

Thank you so much Riny!

Instead of transforming the existing column, is there a way to create an additional column that will return the same information?

Also not where Fund contains (Offshore)

So

Fund D- Accumulation (Offshore) (GBP) 509.715 @ 145.140000

will be

Fund D- Accumulation (Offshore)

I have a Column "Action"

I tried this but did not work ...

= Table.AddColumn(Custom1, "Text Before Delimiter", each if [Action] = "Sell" or [Action] = "Buy" and Text.Contains([Description],"(Offshore") then Text.BeforeDelimiter([Description], " (",1)
else if ([Action] = "Sell" or [Action] = "Buy") and not Text.Contains([Description],"(Offshore") then Text.BeforeDelimiter([Description], " (") else [Description], type text)

Only the items "Sell" in the "Action" column didn't change

TY

Kasey

Let me summarize a bit. You have data that could be:

Fund A - Class A - Income (USD) 17.928 @ 5577.867000
Fund B - Accumulation (GBP) 294.899 @ 339.099100
Fund C - Class A - Income (USD) 17.212 @ 5809.900100
Fund D- Accumulation (Offshore) (GBP) 509.715 @ 145.140000

and you want to create a new column like:

Fund A - Class A - Income
Fund B - Accumulation
Fund C - Class A - Income
Fund D- Accumulation (Offshore)

Correct?

If so, see attached file.

It assumes that (Offshore) is the only exception. What I did is first duplicate the column, replace the (Offshore with a /Offshore. Then extract before  _( , i.e. space parenthesis. Then, change the / back to (

The query is rather straight-forward and it's likely that it can be optimized. But personally, I like simple solutions that don't require any manual M-coding.

If your real data contains more exceptions, could you please provide a more complete example data set. 

If the currency is always the last thing in brackets and you want everything prior to that, you can extract everything before the last delimiter with a calculated column:

=Text.Trim(Text.BeforeDelimiter([Column1], "(", {0, RelativePosition.FromEnd}))

@Velouria

Oh my, forgot about that option to start looking from the End. 🙂

By the way, set the delimiter to " (" and that we don't need the Trim.

I figured it was risky to assume the data would always have that space before the bracket. 😉 Real data is rarely that neat...

Aint that the truth!!

Velouria I couldn't figure out how to implement your suggestion. I inserted a step and copied your code but got an error.

"Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

I also want to try your suggestion as it helps me learn.

Riny your suggestion worked- thank you so much

That was the formula to put in when adding a calculated column. The full code for the step would be:

= Table.AddColumn(Source, "Fund", each Text.Trim(Text.BeforeDelimiter([Column1], "(", {0, RelativePosition.FromEnd})))

I think Column1 would be Description in your real data based on your first post.

Velouria this works !!! So simple, easy when you know how. Thank you!

I am going to use this today and see how I go. Thanks again so much!