grouping and conditional fitlering

[fsim]

hello,

here is my today issue : 
I want to remove the records of a date for each users as soon as there is a day off showing for that day.

 Should I go for some grouping then, filtering ? but how can I filter on somehing that is not allways there ?

userID	Type	ProjectId	hours	Listdate		userID	Type	ProjectId	hours	Listdate
17755201	work	alpha	4	30/03/2022		17755201	work	alpha	4	30/03/2022
17755201	work	beta	4	30/03/2022	==>	17755201	work	beta	4	30/03/2022
17755201	work	alpha	4	31/03/2022		17755201	day off	sick	8	31/03/2022
17755201	day off	sick	8	31/03/2022

[fsim · Accepted solution]

My solution so far:

group by peopleid and listdate , keep all the rows.

then, I created a function to manage those tables :

(mytable) =>
let
Source = Table.AddColumn(mytable, "Custom", each if [Category] = "Time Off" then 1 else 0),
max = List.Max(Source[Custom]),
Custom1 = Table.SelectRows(Source, each [Custom] = max)
in
Custom1

what it does:

it add a column that contains 1 if there is a time off and 0 for the other rows.

I find the maximum of this new column and use it to filter the table. it keeps the 1 if it exist else the 0

and I return the modified table. 

then I simply expand all the tables to get my rows back.

I'm pretty sure I shouldn't use a function to do the job. anyway it's working. 

[amitchandak]

@fsim , This record is not there on right. how to know day off

17755201

work

alpha

4

31/03/2022

[fsim]

hi @amitchandak ,

Sorry, the forum disruped my table a little bit. I know it's a day off because the user encoded it at the same date than the planned work.  Same userid, same Listdate.  I can build a sub table. Then, If one reccord from this subtable is type="time off" then I delete all the other lines, else, I keep them all

[amitchandak]

@fsim , remove duplicate in power query by selecting the columns(or all columns) which are creating the duplicate

[fsim]

nope, because some duplicates are to be kept if there is no day off on that day