Create a new column for returning a specific text from an existing column

[Nekki]

I have a column in a pattern like below where it contains various stars' information, eg age, ethic, nation, etc and the Star name.

I would like to create a new column to only store the value of the Star name [Popular_Star].

Concern is - there are 150+ Stars. If I use "if, then, else", it is not effective. Is there a smarter way to do so? Thanks in advance.

Header	Star Info
Row	Nation; Eastern; Age; Ethic; Popular_Star
Row	Nation; Eastern; Age; Ethic; Popular_Star
Row	Nation; Eastern; Age; Ethic; Popular_Star
Row	Nation; Eastern; Age; Ethic; Popular_Star
Row	Nation; Eastern; Age; Ethic; Popular_Star

[dufoq3 · Accepted solution]

Hi @Nekki, this will work for sample data, but keep in mind, that there are more patterns in your data!

I used logic to extract text with at least one space.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZPdCoIwFIBfZXgtvsCu1LqxX5IIEi9OOjKam8xJ9PYtC9N0UWBkV9uB8237zjkLAmM9wTOgBM+5kAkRDNtZpkKHFgT7kBaEoqmFPIiOOWdGaD6IMeTyCmxALZSknN2pCFQoJfJ4AiyvMN9ucrWLVnxHhEQjfmLkbCJPWBXSkV2esuUE+UBjYFDmumoTg/YGNxGHHC0FSNmQGKq2DnnWfsMX30pTprfU8ItCdGo76jAGeCGA7fuWrsl1kb/p8zeFS8TnRetpfU93S1XT5baebt41DoMx/tP//Il2eAE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Star_info = _t]),
    Ad_StarName = Table.AddColumn(Source, "Star Name", each
        [ a = Text.Split([Star_info], ";"),
          b = List.Select(a, (x)=> Text.Contains(x, " ")){0}?
        ][b], type text)
in
    Ad_StarName

[AlienSx]

    Table.AddColumn(
        Source, "Star name",
        each Splitter.SplitTextByEachDelimiter({"; "}, QuoteStyle.None, true)([Star Info]){1}
    )

[Nekki]

Hi @AlienSx thanks for your comment.

My bad - I am quite new to Power BI that cant able to get your answer.

To elaborate more, for instance, below is the column containing 30 entries for 5 stars. I would like to add a new column to only display the stars' name

Stars

Samuel L. Jackson

Scarlett Johansson

Robert Downey, Jr.

Zoe Saldana

Chris Pratt

Star_Info

UK;Male;Northern;Apple;Blue;Samuel L. Jackson

UK;Male;Eastern;Waterlemon;Blue;Scarlett Johansson

USA;Male;Eastern;Apple;Blue;Robert Downey, Jr.

USA;Eastern;Apple;Blue;Male;Zoe Saldana

Canada;Male;Eastern;Apple;Blue;Chris Pratt

UK;Male;Northern;Apple;Blue;Samuel L. Jackson

UK;Male;Eastern;Waterlemon;Blue;Scarlett Johansson

USA;Male;Eastern;Apple;Blue;Robert Downey, Jr.

USA;Male;Eastern;Apple;Blue;Zoe Saldana

Male;Eastern;Apple;Blue;Chris Pratt;Canada

Samuel L. Jackson;UK;Male;Northern;Apple;Blue

UK;Male;Eastern;Banana;Orange;Scarlett Johansson

USA;Male;Eastern;Apple;Blue;Robert Downey, Jr.

USA;Zoe Saldana;Eastern;Apple;Blue;

Canada;Male;Eastern;Apple;Blue;Chris Pratt

UK;Male;Northern;Apple;Blue;Samuel L. Jackson

UK;Male;Eastern;Banana;Orange;Scarlett Johansson

USA;Male;Eastern;Apple;Blue;Robert Downey, Jr.

USA;Male;Southern;Apple;Blue;Zoe Saldana

Canada;Male;Eastern;Apple;Blue;Chris Pratt

UK;Male;Northern;Samuel L. JacksonApple;Blue

UK;Male;Scarlett Johansson;Eastern;Waterlemon;Blue

USA;Male;Southern;Blue;Robert Downey, Jr.

USA;Male;Southern;Apple;Blue;Zoe Saldana

Canada;Male;Eastern;Apple;Blue;Chris Pratt

UK;Male;Northern;Apple;Blue;Samuel L. Jackson

UK;Male;Eastern;Waterlemon;Blue;Scarlett Johansson

USA;Male;Eastern;Apple;Blue;Robert Downey, Jr.

USA;Male;Eastern;Apple;Blue;Zoe Saldana

Canada;Male;Eastern;Apple;Blue;Chris Pratt

[v-cgao-msft]

Hi @Nekki ,

Please add a custom column use:

Text.AfterDelimiter([Star Info], ";", {0, RelativePosition.FromEnd})

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group