Forum Discussion
pbrainard
Helper III
Helper IIIextract 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 constant...
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
PhilipTreacy
Super User
Super UserHi 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