Forum Discussion

tc5pt's avatar
tc5pt
Regular Visitor
7 years ago
Solved

Remove columns containing a certain value

I have various columns that have a date listed in one row, therefore they contain a "/". None of the other columns I need contain a backslash in any of the rows. I would like to delete the columns co...
Need Help
  • Nolock's avatar
    Nolock
    7 years ago

    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
Nolock's avatar
Nolock
Resident Rockstar
7 years ago

Hi tc5pt,

jep, in the Advanced Editor. Please post your PowerQuery query (without data) and I will help you to integrate the code.

The error message means that there is a comma missing or maybe one in the end of the code before the last IN or something similar.

tc5pt's avatar
tc5pt
Regular Visitor
7 years ago

Nolock 

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])
in
    #"Promoted Headers"

This is what is currently in my advanced editor. It also Changed Type by default but I deleted that step because there were over 100 columns and it created a huge block of code.

 

How would I inject the code you gave?

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    Hi tc5pt,

    here we go:

     

    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]),
    columnsToRemove = List.Select(Table.ColumnNames(#"Promoted Headers"), each Text.StartsWith(_, "Var") or Text.Contains(_, "_prep")),
    result = Table.RemoveColumns(#"Promoted Headers", columnsToRemove)
in
    result
    • tc5pt's avatar
      tc5pt
      Regular Visitor
      7 years ago

      Nolock 

      This doesn't seem to be working and I'm not sure why. The columns' data type is text. I have made sure not to add any steps between (ie. no additional code has been added). Any ideas?

      • HotChilli's avatar
        HotChilli
        Community Champion
        7 years ago

        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?