Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Klal700
Frequent Visitor

Remove Records based on Date Condition

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?

 

 

Klal700_1-1693478577415.png

 

 

1 ACCEPTED SOLUTION
JoeBarry
Solution Sage
Solution Sage

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

     

  • JoeBarry_0-1693480111121.png
  • 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

 

View solution in original post

3 REPLIES 3
JasperDJ
Helper I
Helper I

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.

JasperDJ_0-1693481095145.png

= 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:

JasperDJ_1-1693483664261.png

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

 

 

JoeBarry
Solution Sage
Solution Sage

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

     

  • JoeBarry_0-1693480111121.png
  • 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

 

Hi @JoeBarry ,

 

Thank you! That gives me exactly what I need. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors