extract everything except...

[pbrainard]

I have a delimited list of items. I want to be able to create a new column and extract a specific portion on the list. I want to extract the 'other' comments, from the constants. The list of constants is:

bonus program

recgonition efforts

wellness program

anniversaries

other (this is the part I want to extract)

So in line one, below, I want to extract - more vending options

in line two, I want to extract - more time off

in line three, I don't want to extract anything (maybe a null value to the new column)

bonus program;recognition efforts;wellness program;anniversaries;more vending options;
recognition efforts;bonus program;more time off;wellness program;
wellness program;bonus program;anniversaries;

[PhilipTreacy · Accepted solution]

Hi @pbrainard 

Download sample PBIX with the following code

You can do this in Power Query with this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY5BCsMwDAS/InzOD/wU44PTro0gloLkpt8vzSWY5LrszmxKYVX5OO2mzUqPhpc24cEqhFrVhscvtk3gV6mI8AHzYgyPXQ10QN4sjXT/Tz2GvKTwBJt153ZwB2mtd9FJuaUzYj4Tcv4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Comments = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Comments", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveItems(Text.Split([Comments], ";"), {"bonus program", "recognition efforts", "wellness program", "anniversaries"})))
in
    #"Added Custom"

Regards

Phil