Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 absence data. The data is displayed by showing a new row for each days absence. What i`m trying to do is group the consecutive days. For example we have an employee who was off sick for three days (14/01/2019 - 16/01/2019) and the report shows a new line for each day. I need to be able to group these 3 days that I can highlight as one absence.
I have a query that has been written and mostly works really well. It recognises if the absence continues from Friday to Monday and groups these together which is really important. What it does not do to recognise public/ bank holidays or any specific site closures so if the absence falls over these the consecutive days grouping breaks and I have two instances. These closure days are likely to change slightly annually so I was thinking a seperate table that I could update as and when i needed would be the way forward but i dont know how to add this to the query.
Also, the input table has two columns (ResID, and Department) that are not populated onto the output table - any help on getting this to populate would be great.
Sample file is below (excel file - will move to Power BI later)
Please see current query below:
let
fnDateList = (MyTable) =>
let
DateStart = List.Min(MyTable[Date]),
DateEnd = List.Max(MyTable[Date]),
#"List Dates" = List.Dates(DateStart, Duration.Days(DateEnd - DateStart)+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(#"List Dates", Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Ignore),
#"Changed Type Date" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
#"Merged MyTable" = Table.NestedJoin(#"Changed Type Date", {"Date"}, MyTable, {"Date"}, "Sick Day", JoinKind.LeftOuter),
#"Expanded MyTable" = Table.ExpandTableColumn(#"Merged MyTable", "Sick Day", {"Employee Number", "Days"}, {"Employee Number", "Sick Days"}),
#"Added Weekday" = Table.AddColumn(#"Expanded MyTable", "Weekday", each Date.DayOfWeek([Date], Day.Monday), Int64.Type),
#"Filtered Weekends" = Table.SelectRows(#"Added Weekday", each [Weekday] < 5),
#"Sorted Dates" = Table.Sort(#"Filtered Weekends",{{"Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Dates", {"Employee Number"}, {{"From", each List.Min([Date]), type date}, {"To", each List.Max([Date]), type date}, {"Sick Days", each List.Sum([Sick Days]), type number}}, GroupKind.Local),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Employee Number] <> null and [Employee Number] <> ""),
#"Added Dates" = Table.AddColumn(#"Filtered Rows", "Sickness Dates", each Text.Combine({Text.From([From]), if [To] > [From] then Text.From([To]) else null}," - "), type text),
#"Selected Columns" = Table.SelectColumns(#"Added Dates",{"Employee Number", "Sickness Dates", "Sick Days"})
in
#"Selected Columns",
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Number", Int64.Type}, {"ResID(T)", type text}, {"Date", type date}, {"Abs. code", type text}, {"Days", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Number", "ResID(T)", "Abs. code"}, {{"Data", each fnDateList(_), type table}}),
#"Combined Data" = Table.Combine(#"Grouped Rows"[Data]) in
#"Combined Data"
Any help is appreciated.
Thanks,
Brendan
I haven't read your code, but see if this scheme can help you.
A list of sick days is created for each employee; the holiday days contained in a specific table are removed from this list and then a function counts the groups of consecutive days.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZnJbtswEIZfRdA5sEhJluxjlhZJixZBXPQS5MA6hC1UlVE5C/L2JVO0kcnJLObFi4CPQ3IW/kPd3uZaKVXlJ/mn3XbIVr+6h637c3njPnShdVEqvXS/V1fnn/2jmVL53QlB1UdR86OoBqPqV8pmZ/1us9n/o8oCneB7EDo/GKoLdANhSKuiXGJY4zEzdGubfXgyw3+wLcopdu2+1UxzOL2IwbIlQUdBIG1xEayQbdHtTQWskQXqFjW5cA+u7YMds7PHcdw9brZTN7aBQ9SsZqE+g0JnqlmjWGx56NBwd9EZV+rY1c4LYKN0xUGbwyT5iyqW1abQQPSyWSgq5hwWThrWapdQLFYs1Ifjsat1+wTYfYsLl/En+Y1Zb22fXbw829H9/dgNZljbv6kXBtW0uOjyNYHWvx/tPvva2cH8CAZYRkEp48OECHecHqEMliAfwe9f2gh1AVQ+2QAaylDBRs6Dwy+MIXoEh6OpznCmAichGkKDsygF63AZARWPySy8fPg2mrV9yS52Y98NmyCigLo1mQGBV4HkkfI1cRxqf6qdDvejNfts9ZC9/nxGwylMS9YImFjRviKd96YbbfZ91/dETuaHxxxBLwsdH5JzLh2KAtnMHY3JQYrWqC6k6VC/StZdxvL3IGwIPJDcUQXC8So+wQQrr4ooY0R0NHMRHZUsER1VKxEdnRsiWqdtelwmZXgkPGR4ms/jcJXhiTsflWgRHgsOGZ7m91gwyfC0nS/T8i2WBzI8ze/x0SzA66QiWce9iYhOydY6qUDXSQUaENciOtFjKZkaqig5nuLyFr7kEOCQDuWrOKirEsk4vCUixRSqwkvtHlw9mSG7tE7+duufvqVemd767wZw3NR46buQ095fLJ2ZcTDj/QTG7/kIFGunUVTDfTAPfqdn4cENdAMxr5gsriJQuAXbzYoLx+eYWzJv2i3Yo9U8ywvotofNAnbblslCGck2HIsO52WeZfiCi2n5nVsFpqc8DVzOMMMTqiL8jNT4uUfBqE6iYFQlUTB64FIwfuSRNHrikfTxxUSHr6rEdJKz8X6EpFGRQ9F4N0LSSf7GexGSTtpzvBMh6aQUw/sQkk7wN3i92b7B/j3khen2L9kXa+53z1NdtAziND/UdCjrL6iwN5mEYWDSTMPhpN/guz8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Employee Number" = _t, #"ResID(T)" = _t, Department = _t, Date = _t, #"Abs. code" = _t, Days = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Number", Int64.Type}, {"ResID(T)", type text}, {"Department", type text}, {"Date", type date}, {"Abs. code", type text}, {"Days", type number}},"en-US"),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Number"}, {{"sickDays", each sickGrps(List.Difference(_[Date],holidays))}, {"sd", each Table.RowCount(_), type number}})
in
#"Grouped Rows"
sickGrps
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}))
in
grp
holidays
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZnJbtswEIZfRdA5sEhJluxjlhZJixZBXPQS5MA6hC1UlVE5C/L2JVO0kcnJLObFi4CPQ3IW/kPd3uZaKVXlJ/mn3XbIVr+6h637c3njPnShdVEqvXS/V1fnn/2jmVL53QlB1UdR86OoBqPqV8pmZ/1us9n/o8oCneB7EDo/GKoLdANhSKuiXGJY4zEzdGubfXgyw3+wLcopdu2+1UxzOL2IwbIlQUdBIG1xEayQbdHtTQWskQXqFjW5cA+u7YMds7PHcdw9brZTN7aBQ9SsZqE+g0JnqlmjWGx56NBwd9EZV+rY1c4LYKN0xUGbwyT5iyqW1abQQPSyWSgq5hwWThrWapdQLFYs1Ifjsat1+wTYfYsLl/En+Y1Zb22fXbw829H9/dgNZljbv6kXBtW0uOjyNYHWvx/tPvva2cH8CAZYRkEp48OECHecHqEMliAfwe9f2gh1AVQ+2QAaylDBRs6Dwy+MIXoEh6OpznCmAichGkKDsygF63AZARWPySy8fPg2mrV9yS52Y98NmyCigLo1mQGBV4HkkfI1cRxqf6qdDvejNfts9ZC9/nxGwylMS9YImFjRviKd96YbbfZ91/dETuaHxxxBLwsdH5JzLh2KAtnMHY3JQYrWqC6k6VC/StZdxvL3IGwIPJDcUQXC8So+wQQrr4ooY0R0NHMRHZUsER1VKxEdnRsiWqdtelwmZXgkPGR4ms/jcJXhiTsflWgRHgsOGZ7m91gwyfC0nS/T8i2WBzI8ze/x0SzA66QiWce9iYhOydY6qUDXSQUaENciOtFjKZkaqig5nuLyFr7kEOCQDuWrOKirEsk4vCUixRSqwkvtHlw9mSG7tE7+duufvqVemd767wZw3NR46buQ095fLJ2ZcTDj/QTG7/kIFGunUVTDfTAPfqdn4cENdAMxr5gsriJQuAXbzYoLx+eYWzJv2i3Yo9U8ywvotofNAnbblslCGck2HIsO52WeZfiCi2n5nVsFpqc8DVzOMMMTqiL8jNT4uUfBqE6iYFQlUTB64FIwfuSRNHrikfTxxUSHr6rEdJKz8X6EpFGRQ9F4N0LSSf7GexGSTtpzvBMh6aQUw/sQkk7wN3i92b7B/j3khen2L9kXa+53z1NdtAziND/UdCjrL6iwN5mEYWDSTMPhpN/guz8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Employee Number" = _t, #"ResID(T)" = _t, Department = _t, Date = _t, #"Abs. code" = _t, Days = _t]),
days = Table.SelectColumns(Source,{"Date"}),
#"Changed Type" = Table.TransformColumnTypes(days,{{"Date", type date}},"en-US"),
hol= List.Transform(List.Random(50,11), each #"Changed Type"[Date]{Number.RoundDown(_*120)})
in
hol
@Anonymous
Thanks for this. It sounds like it incorporates all the things I need.
Unfortunately, this is a little advanced for my Power Query knowledge. Can you please provide some guidance on how to introduce these three steps into my query.
Sorry if thats a silly question.
Brendan
try this:
let
/* fnDateList = (MyTable) =>
let
DateStart = List.Min(MyTable[Date]),
DateEnd = List.Max(MyTable[Date]),
#"List Dates" = List.Dates(DateStart, Duration.Days(DateEnd - DateStart)+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(#"List Dates", Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Ignore),
#"Changed Type Date" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
#"Merged MyTable" = Table.NestedJoin(#"Changed Type Date", {"Date"}, MyTable, {"Date"}, "Sick Day", JoinKind.LeftOuter),
#"Expanded MyTable" = Table.ExpandTableColumn(#"Merged MyTable", "Sick Day", {"Employee Number", "Days"}, {"Employee Number", "Sick Days"}),
#"Added Weekday" = Table.AddColumn(#"Expanded MyTable", "Weekday", each Date.DayOfWeek([Date], Day.Monday), Int64.Type),
#"Filtered Weekends" = Table.SelectRows(#"Added Weekday", each [Weekday] < 5),
#"Sorted Dates" = Table.Sort(#"Filtered Weekends",{{"Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Dates", {"Employee Number"}, {{"From", each List.Min([Date]), type date}, {"To", each List.Max([Date]), type date}, {"Sick Days", each List.Sum([Sick Days]), type number}}, GroupKind.Local),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Employee Number] <> null and [Employee Number] <> ""),
#"Added Dates" = Table.AddColumn(#"Filtered Rows", "Sickness Dates", each Text.Combine({Text.From([From]), if [To] > [From] then Text.From([To]) else null}," - "), type text),
#"Selected Columns" = Table.SelectColumns(#"Added Dates",{"Employee Number", "Sickness Dates", "Sick Days"})
in
#"Selected Columns",
*/
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})),
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"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 fnDateList(_), type table}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Number", "ResID(T)", "Abs. code"}, {{"Data", each grp(List.Difference(_[Date],holidays))}})//,
//each sickGrps(List.Difference(_[Date],holidays))
//#"Combined Data" = Table.Combine(#"Grouped Rows"[Data])
in
#"Grouped Rows"//#"Combined Data"
if it works (I don't have the exact knoledge of your data. For this reason a lot of thinks can go wrong) , then you can clean the code from the parts commented out.
@Anonymous
Thanks. This has not worked.
Do i need to setup a seperate table that holds the holiday information. Also you mention in your reply that i can clean the code from the parts commented out - sorry i`m not sure what you mean here - which parts are commented out.
Thanks,
Brendan
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.
@Anonymous
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
62 | |
18 | |
16 | |
13 |