How to extract a column based on it's content in PowerBI

Question

I have a column in my table which looks like below.

ResourceIdentifier
------------------
arn:aws:ec2:us-east-1:7XXXXXX1:instance/i-09TYTYTY79716
arn:aws:glue:us-east-1:5XXXXXX85:devEndpoint/etl-endpoint
i-075656565f7fea3
i-02c3434343f22
qa-271111145-us-east-1-raw
prod-95756565631-us-east-1-raw
prod-957454551631-us-east-1-isin-repository
i-02XXXXXXf0

I want a new column called 'Trimmed Resource Identifier' which looks at ResourceIdentifier and if the value starts with "arn", then returns value after last "/", else returns the whole string.

For eg.

arn:aws:ec2:us-east-1:7XXXXXX1:instance/i-09TYTYTY79716 ---> i-09TYTYTY797168

i-02XXXXXXf0 --> i-02XXXXXXf0

How do I do this ? I tried creating a new column called "first 3 letters" by extracting first 3 letters of the ResourceIdentifier column but I am getting stuck at the step of adding conditional column. Please see the image below.

Is there a way I can do all of this in one step using DAX instead of creating a new intermediate column ?

Many Thanks

Alexis Olson Alexis Olson · Accepted Answer · 2020-12-01T20:13:21

The GUI is too simple to do exactly what you want but go ahead and use it to create the next step, which we can then modify to work properly.

Filling out the GUI like this

will produce a line of code that looks like this (turn on the Formula Bar under the View tab in the query editor if you don't see this formula).

= Table.AddColumn(#"Name of Previous Step Here", "Custom",
    each if Text.StartsWith([ResourceIdentifier], "arn") then "output" else [ResourceIdentifier])

The first three letters bit is already handled with the operator I chose, so all that remains is to change the "output" placeholder to what we actually want. There's a handy Text.AfterDelimiter function we can use for this.

Text.AfterDelimiter([ResourceIdentifier], "/", {0, RelativePosition.FromEnd})

This tells it to take the text after the first / (starting from the end). Replace "output" with this expression and you should be good to go.

How to extract a column based on it's content in PowerBI

1 Answers