Forum Discussion
Solved
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...
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
dufoq3
Community Champion
Community ChampionHi 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