Forum Discussion
Custom Power Query Filter
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.
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"
2 Replies
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"Hi Anonymous , this might be a good one for ImkeF who is the master of m!
Nathaniel
