Forum Discussion
Create a new column for returning a specific text from an existing column
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 |
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
4 Replies
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_StarNameTable.AddColumn( Source, "Star name", each Splitter.SplitTextByEachDelimiter({"; "}, QuoteStyle.None, true)([Star Info]){1} )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 Hi Nekki ,
Please add a custom column use:Text.AfterDelimiter([Star Info], ";", {0, RelativePosition.FromEnd})Best Regards,
Gao
Community Support TeamIf 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
