Log In
Register
Home
April 6, 2022
Offline
Hi
I am seeking advice on how I can trim the text below in Excel Power Query :-
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 (GBP) 509.715 @ 145.140000
to be like .....
Fund A - Class A - Income
Fund B - Accumulation
Fund C - Class A - Income
Fund D- Accumulation
Thank you in advance
Kasey
See Answer
Moderators
January 31, 2022
Offline
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}})
April 6, 2022
Offline
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
Moderators
January 31, 2022
Offline
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.
Answers Post
Trusted Members
Moderators
November 1, 2018
Online
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}))
Moderators
January 31, 2022
Offline
@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.
Trusted Members
Moderators
November 1, 2018
Online
I figured it was risky to assume the data would always have that space before the bracket. 😉 Real data is rarely that neat...
April 6, 2022
Offline
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
Trusted Members
Moderators
November 1, 2018
Online
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.
1 Guest(s)
