Forum Discussion
Remove columns based on whether they contain a string
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
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"
4 Replies
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"Nice! If there is another way I will try it but for now yours work very well - thank you!
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.
Hello, the solution from Anonymous works nicely 🙂
