Forum Discussion

pbrainard's avatar
pbrainard
Helper III
4 years ago
Solved

extract everything except...

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;

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    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

1 Reply

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    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