Forum Discussion
Bfaws
Helper III
6 years agoGrouping sickness absence
Hello Community, I was hoping someone could help. I`m able to run a report on Sickness Absence data from one of our systems at work. The report shows a number of employees and thier sickness...
Anonymous
6 years agoNot applicable
this is the code
let
grp=(dates as list)=>
let
days=List.Count(dates)
in List.Accumulate({1..days-1}, 1,(s,c)=>s+Number.From(Date.AddDays(dates{c-1},1)<>dates{c})),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Number", Int64.Type}, {"ResID(T)", type text}, {"Date", type date}, {"Abs. code", type text}, {"Days", type number}},"en-US"),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Number", "ResID(T)", "Abs. code"}, {{"Data", each grp(List.Difference(_[Date],holidays))}})
in
#"Grouped Rows"
this is the output table
this is the holiday table I used:
You have to create a reeal "holiday" table.
this is the input table, that I called "Source"
PS
If this is not what you expect or it is not working, in order to help you, you must provide more detailed information of what you have done and the code and data used.
Bfaws
Helper III
6 years agoAnonymous
Thanks again for your ongoing support on this. I`m afraid i cant replicate.
Please see link to .pbix file below that has the source data added as a table and the holidays added as a seperate table.
Please let me know fi you need more information.
Thanks,
Brendan