Remove columns based on whether they contain a string

[ValeriaBreve]

Hello,

I would like to remove columns from my query if they contain a certain string.

Ex. below. If I wanted to remove all columns in the query containing the string "p", then Column3 would be removed:

Column1	Column2	Column3
e	e	g
f	d	h
g	c	u
h	a	i
b	q	j
d	b	n
s	h	p

Is this possible?

Thanks!

Kind regards

Valeria

[AnkitKukreja]

Hi! @ValeriaBreve 

As far as I know, that's not possible. But you can use the option to remove the column directly to remove that unwanted column.

[ValeriaBreve]

Hello, the solution from @Anonymous works nicely 🙂

[Anonymous · Accepted solution]

Hello, I tried to do it but I did a lot of steps, I think there must be something more easy...

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlXSAeN0pVidaKU0ICsFiDPAvHQgKxmIS8G8DCArEYgzwbwkIKsQiLPAPJAekEgemFcMNkFHqUApNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
Custom1 = Table.ToColumns(Source),
Custom2 = List.Transform(Custom1,each List.Contains(_,"p")),
#"Converted to Table" = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Custom3 = Table.ColumnNames(Source),
#"Converted to Table1" = Table.FromList(Custom3, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index1" = Table.AddIndexColumn(#"Converted to Table1", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index1", "Custom", each #"Converted to Table"[Column1]{[Index]}),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = false then [Column1] else null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Custom.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Custom.1", "Custom"}}),
Custom4 = Source,
#"Removed Other Columns1" = Table.SelectColumns(Custom4,#"Renamed Columns"[Custom])
in
#"Removed Other Columns1"

[ValeriaBreve]

Nice! If there is another way I will try it but for now yours work very well - thank you!