locked
Filtering Multiple Columns Based on Header Content RRS feed

  • Question

  • Hi am a total  beginner @Power Query trying to filter multiple columns based on header content

    This is the first time I am trying to piece together a query, I've tried looking around the forums for solid examples, but am stuck

    on this one problem:

    I am trying to filter columns with "/" and the text "Grand"

    Code Example:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"04/01/2016", Int64.Type}, {"04/04/2016", Int64.Type}, {"04/11/2016", Int64.Type}, {"04/18/2016", Int64.Type}, {"04/25/2016", Int64.Type}, {"05/02/2016", Int64.Type}, {"APRIL TTL", Int64.Type}, {"05/09/2016", Int64.Type}, {"05/16/2016", Int64.Type}, {"05/23/2016", Int64.Type}, {"05/30/2016", Int64.Type}, {"MAY TTL", Int64.Type}, {"06/06/2016", Int64.Type}, {"06/13/2016", Int64.Type}, {"06/20/2016", Int64.Type}, {"06/27/2016", Int64.Type}, {"JUNE TTL", Int64.Type}, {"Total Q 1 2017", Int64.Type}, {"07/04/2016", Int64.Type}, {"07/11/2016", Int64.Type}, {"07/18/2016", Int64.Type}, {"07/25/2016", Int64.Type}, {"08/01/2016", Int64.Type}, {"JULY TTL", Int64.Type}, {"08/08/2016", Int64.Type}, {"08/15/2016", Int64.Type}, {"08/22/2016", Int64.Type}, {"08/29/2016", Int64.Type}, {"AUGUST TTL", Int64.Type}, {"09/05/2016", Int64.Type}, {"09/12/2016", Int64.Type}, {"09/19/2016", Int64.Type}, {"09/26/2016", Int64.Type}, {"SEPTEMBER TTL", Int64.Type}, {"Total Q 2 2017", Int64.Type}, {"10/03/2016", Int64.Type}, {"10/10/2016", Int64.Type}, {"10/17/2016", Int64.Type}, {"10/24/2016", Int64.Type}, {"10/31/2016", Int64.Type}, {"OCTOBER TTL", Int64.Type}, {"11/07/2016", Int64.Type}, {"11/14/2016", Int64.Type}, {"11/21/2016", Int64.Type}, {"11/28/2016", Int64.Type}, {"NOVEMBER TTL", Int64.Type}, {"12/05/2016", Int64.Type}, {"12/12/2016", Int64.Type}, {"12/19/2016", Int64.Type}, {"12/26/2016", Int64.Type}, {"12/31/2016", Int64.Type}, {"DECEMBER TTL", Int64.Type}, {"Total Q 3 2017", Int64.Type}, {"01/02/2017", Int64.Type}, {"01/09/2017", Int64.Type}, {"01/16/2017", Int64.Type}, {"01/23/2017", Int64.Type}, {"01/30/2017", Int64.Type}, {"JANUARY TTL", Int64.Type}, {"02/06/2017", Int64.Type}, {"02/13/2017", Int64.Type}, {"02/20/2017", Int64.Type}, {"02/27/2017", Int64.Type}, {"FEBRUARY TTL", Int64.Type}, {"03/06/2017", Int64.Type}, {"03/13/2017", Int64.Type}, {"03/20/2017", Int64.Type}, {"03/27/2017", Int64.Type}, {"MARCH TTL", Int64.Type}, {"Total Q 4 2017", Int64.Type}, {"Grand Total", Int64.Type}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1", "Column4"}),
        #"Filled Down" = Table.FillDown(#"Removed Columns",{"Column2"}),
        #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Column2", "Family Name"}, {"Column3", "Part #"}, {"Column5", "Comparison"}}),
        #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Comparison] = "DELTA")),
        #"ColumnsToRemove" = List.Select(Table.ColumnNames(#"Filtered Rows"), each Text.Contains(_, "/") or Text.Contains(_, "Grand")) in Table.RemoveColumns("#Filtered Rows", #"ColumnsToRemove"))	
    in
        #"ColumnsToRemove"


    Sunday, April 24, 2016 11:24 AM

Answers

  • Hi DDLabry,

    Try to change your last step.

       #"ColumnsToRemove" = List.Select(Table.ColumnNames(#"Filtered Rows"), each Text.Contains(_, "/") or Text.Contains(_, "Grand")),
       RemoveColumns = Table.RemoveColumns("#Filtered Rows", #"ColumnsToRemove")
     in
       RemoveColumns

    or

        #"ColumnsToRemove" = List.Select(Table.ColumnNames(#"Filtered Rows"), each not (Text.Contains(_, "/") or Text.Contains(_, "Grand"))),
        RemoveColumns = Table.SelectColumns("#Filtered Rows", #"ColumnsToRemove")
    in
        RemoveColumns   
    Regards

    Monday, April 25, 2016 10:16 AM