Forum Discussion
Extract specific values from an column that contains a delimited list
I have a column that has a list of information delimited by ",". Example:
CodeA-45341,CodeB-35841,CodeC-87863,CodeA-45341,CodeB-35841,CodeC-87863
I want to extract all of the "CodeA"s. So from that example, the output would be:
CodeA-45341,CodeA-45341
A Screenshot of more samples:
I can handle if there are only two occurrences by combining:
Text.Middle([Input],Text.PositionOf([Input],"CodeA", Occurrence.First), 11)
Text.Middle([Input],Text.PositionOf([Input],"CodeA", Occurrence.Last), 11)
Using Occurrence.All provides a list of numbers that the function doesn't handle. I assume there is a way to handle it with that, but it's just not coming to me.
Some items to note:
"CodeA" information is not static like it is in my example. It could be followed by any series of numbers and letters.
"CodeA" may not exist, and should result in a null cell.
The "Input" column can have any number of items to parse though
Hi Anonymous ,
Try this in a new custom column:
List.Select( Text.Split([Input], ","), each Text.Contains(_ , "CodeA") )From here you can either extract to new rows or extract values, whatever you need.
Pete
2 Replies
- Vijay_A_Verma
Most Valuable Professional
In a custom column, use following
= if Text.Contains([Input],"CodeA") then Text.Combine(List.Select(Text.Split([Input],","),(x)=>Text.Start(x,5)="CodeA"),",") else nullSee the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs5PSXXUNTE1NjHUAbGddI1NLaBsZ10LcwszYx0i1CjF6kDMAsqZACGc76hraGRsYmpmbmFgYGikFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Input],"CodeA") then Text.Combine(List.Select(Text.Split([Input],","),(x)=>Text.Start(x,5)="CodeA"),",") else null) in #"Added Custom" - BA_Pete
Super User
Hi Anonymous ,
Try this in a new custom column:
List.Select( Text.Split([Input], ","), each Text.Contains(_ , "CodeA") )From here you can either extract to new rows or extract values, whatever you need.
Pete
