Custom Power Query Filter

[Anonymous]

Hi all. I need to remove specific rows that have "code ABC" with date before 8/15/2019 and rows that have "code XYZ" with date before 7/29/2019. These are in two different columns. I cannot simply filter the table afterwards because the table is connected to many other excel files that use its data. I think there needs to be a custom step written, but I'm not sure how to do that. 

[Anonymous · Accepted solution]

Add a custom column that shows the records that you want to exclude. Perhaps you call the column "Exclude"

[code] = "ABC" and [date] < #date(2019, 8, 15) or [code] = "XYZ" and [date] < #date(2019, 7, 29). Now filter with Exclude = false.

Below is some sample code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"code", type text}, {"date", type datetime}}),
    #"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"date", DateTime.Date, type date}}),
    AddExclude = Table.AddColumn(#"Extracted Date", "Exclude", each [code] = "ABC" and [date] < #date(2019, 8, 15) or [code] = "XYZ" and [date] < #date(2019, 7, 29)),
    #"Filtered Rows" = Table.SelectRows(AddExclude, each ([Exclude] = false))
in
    #"Filtered Rows"

[Nathaniel_C]

Hi @Anonymous , this might be a good one for @ImkeF who is the master of m!

Nathaniel