Remove Records based on Date Condition

[Klal700]

Hi All, 

I have an Employee table in the below format. For "PersonalIDs" that have records with "Job_Start_Date" of both before and after Jan 1, 2022 (like the example below), I would like to remove any records before Jan 1, 2022 and keep all records on or after Jan 1, 2022. I also have PersonalIDs that only have records before Jan 1, 2022. For such cases, do nothing and keep those records. 

How do I achieve this in Power Query?

[JasperDJ]

Hi @Klal700 ,

You can adjust a filter to conditionally filter records, in practice the fastest way is to just add conditional column with an IF AND condition. Like IF employee id =5 and date <#date.

= Table.AddColumn(#"Changed Type1", "Conditional column", each if [Employee ID]="5" and [dates]<#date(2022,6,1) then 1 else 0)

The other route is to re-write the filter statement:

= Table.SelectRows(#"Added Custom2", each if [Employee ID] = "5" and [dates]<#date(2022,6,1) then ([Employee ID] <> "5") else true)

[JoeBarry · Accepted solution]

Hi @Klal700 

Not sure if this the best solution, but it's at most a doable workaround.

• Create a duplicate of the table and rename it something like Before 2022
• In the orginal table filter the Job_Start_date >= 01/01/2022
• Untick enable query for the duplicate table.
• Create a custom column called lets say Cut Off and Enter "01/01/2022" and convert to date
• Highlight the PersonalID and in the Ribbon, Choose Group By
• Click on Advanced
• Rename the Column to MaxDate
• Click on Add aggregation
• Call the new column All and the Operation should be All Rows
• Press ok
• 
• In the Max column you will see the Max Job-Start_Date
• Expand the All column except for the PersonalID
• Create a filter on the Max date column <= doesn't matter what date you choose, we will adapt the code next
• Change the code after the each part to 

each [Max] < [Cut Off])​

• This will filter all Employees who have a max Job_Start_Date before 01/01/2022
• Remove the Max Date and Cut Off Columns

Once you have this return to the original table and in the ribbon, click on append queries and choose the duplicated table to append.

This will give you the result you need.

Thanks

Joe

If this post helps, then please Accept it as the solution

[Klal700]

Hi @JoeBarry ,

Thank you! That gives me exactly what I need.