Forum Discussion
Remove columns containing a certain value
Hi tc5pt,
I've tried to integrate it into the code you mentioned earlier. Just replace all occurrences of "Source" with #"Promoted Headers" and it should work in your Excel file.
let Source = Excel.Workbook(File.Contents("C: Source"), null, true), #"1_Sheet" = Source{[Item="1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"1_Sheet", [PromoteAllScalars=true]), // get columns which contains any slash among values ColumnsToRemove = List.Select( // get a list of all columns Table.ColumnNames(#"Promoted Headers"), (columnName) => let // get all values of a columns ColumnValues = Table.Column(#"Promoted Headers", columnName), // go through values and stop when you find the first occurence of a text containing a slash // if there is a value with a slash, return true else false ContainsSlash = List.AnyTrue(List.Transform(ColumnValues, each Text.Contains(_, "/"))) in ContainsSlash ), // remove columns Result = Table.RemoveColumns(#"Promoted Headers", ColumnsToRemove) in Result
Hi, looking at the code sample you provided first and the requirement. They don't do the same thing. The code sample looks for column names that begin with "Var" or contain "_prep" and removes the column.
Nolock(respect for the contribution you make to this site) helped you remove the syntax errors and, I think, assumed that the sample code was what you wanted.
I think (correct me if i'm wrong) that you want to remove columns where there is a row value that has "/" in it.
So, if the data looked like this:
column1 column2 column 3
A FRE 6/2/2019
joe 23/4 x
sd gt y
you want to delete columns 2 and 3. Yes?
I already changed that part of the code. I had changed the source and item/sheet names for the post and changed that back as well.
And yes, that's correct. Is it perhaps not the right code for that purpose?
Thank you HotChilli
I'm very sorry, I unterstood the task wrong at the beginning.
I've written another code which removes columns that have at least one value containing a slash.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUrUB5FJQKwUqxMNZoBwsn4KTCgZxNBRMtQ3AovEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), // get columns which contains at least one value which contains a slash ColumnsToRemove = List.Select( // get a list of all columns of the table Source Table.ColumnNames(Source), (columnName) => let // get all values of a column ColumnValues = Table.Column(Source, columnName), // go through values and stop when you find the first occurence of a text containing a slash // if there is a value with a slash, return true else false ContainsSlash = List.AnyTrue(List.Transform(ColumnValues, each Text.Contains(_, "/"))) in ContainsSlash ), // remove columns Result = Table.RemoveColumns(Source, ColumnsToRemove) in ResultHi tc5pt,
I've tried to integrate it into the code you mentioned earlier. Just replace all occurrences of "Source" with #"Promoted Headers" and it should work in your Excel file.
let Source = Excel.Workbook(File.Contents("C: Source"), null, true), #"1_Sheet" = Source{[Item="1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(#"1_Sheet", [PromoteAllScalars=true]), // get columns which contains any slash among values ColumnsToRemove = List.Select( // get a list of all columns Table.ColumnNames(#"Promoted Headers"), (columnName) => let // get all values of a columns ColumnValues = Table.Column(#"Promoted Headers", columnName), // go through values and stop when you find the first occurence of a text containing a slash // if there is a value with a slash, return true else false ContainsSlash = List.AnyTrue(List.Transform(ColumnValues, each Text.Contains(_, "/"))) in ContainsSlash ), // remove columns Result = Table.RemoveColumns(#"Promoted Headers", ColumnsToRemove) in Result