Extract specific values from an column that contains a delimited list

[Anonymous]

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

[BA_Pete · Accepted solution]

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

[Vijay_A_Verma]

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 null

See 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"