Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
Hi @Klal700
Not sure if this the best solution, but it's at most a doable workaround.
each [Max] < [Cut Off])
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 @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)
Hi @Klal700
Not sure if this the best solution, but it's at most a doable workaround.
each [Max] < [Cut Off])
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