How to detect consecutive groups of records within date range?

[JanoLehocky]

Good afternoon Power BI Community!

I work at Suncor and am trying to see if there is a way to solve this reporting puzzle.

Background

We are trying to create a report that can be refreshed by feeding it source data to figure out which employees are away from work for periods longer than 12 days that contain at least ONE absense code (A/A Type column) of type MUA.

The other A/A Type Codes that could make up the 12 days could be:

DBH
VAC
TRV
LNP

Other codes are of no consequence.

Example:

Someone is coded with 4 MUA entries from April 1 to 4 and then VAC  April 5 to 13. Since this is more than 12 days and the unbroken stretch of time off includes MUA, I would want to have this reported on. If it was an unbroken stretch of 12 days or less containing MUA... I don't care.

Reporting Goal

Identify specific people with the number of days away from work that number greater in 12 that contained MUA absence codes that were consecutive days off.

- # of occurances these stretches of more than 12 days

- # of days away for each occurance

- # of MUA days off used in each stretch of consec days off 

Here is a link to some sample data:

https://docs.google.com/spreadsheets/d/1_9h47qnO_5vWJ0RRLpDVXNv3ZiaoZ4kn/edit?usp=sharing&ouid=10053...

Please reach out to me if you have any questions or suggestions!

Thank you

Jano Lehocký

[JanoLehocky]

Thanks Nate!!

I have what I need now.  Thank you for introducing me to this new to me function!

Cheers

Jano

[watkinnc]

Oh, I get it.  Of all of the stretches of 12+ days containing the 5 codes (AbsentCode), which of those stretches contain "MUA" (AbsentMUA)?

Here you are:

let
Source = Excel.CurrentWorkbook(){[Name="Table1_2"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Org. Text", type text}, {"WS rule", type text}, {"Name", type text}, {"A/AType", type text}, {"Pers.No.", Int64.Type}, {"Absence/Attendance hours", Int64.Type}, {"Planned working hours", Int64.Type}, {"Start Date A/A Type", type date}, {"Created on", type date}, {"Time", type time}}),

#"Added Custom" = Table.AddColumn(#"Changed Type", "AbsentCode", each List.Contains({"MUA", "LNP", "VAC", "DBH","TRV"}, [#"A/AType"]), type text),

Custom2 = Table.AddColumn(#"Added Custom", "AbsentMUA", each List.Contains({"MUA"},  [#"A/AType"]), type text),

#"Grouped Rows" = Table.Group(Custom2, {"AbsentCode"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"MUADaysPresent", each List.Max([AbsentMUA]), type text}, {"Start Date", each List.Min([#"Start Date A/A Type"]), type nullable date}, {"End Date", each List.Max([#"Start Date A/A Type"]), type nullable date}, {"Details", each _, type table [Org. Text=nullable text, WS rule=nullable text, #"Pers.No."=nullable number, Name=nullable text, #"Start Date A/A Type"=nullable date, #"A/AType"=nullable text, #"Absence/Attendance hours"=nullable number, Planned working hours=nullable number, Created on=nullable date, Time=nullable time, AbsentCode=text, AbsentNonMUA=text]}}, GroupKind.Local),

#"Filtered Rows2" = Table.SelectRows(#"Grouped Rows", each [Count] > 12),

#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows2", each ([AbsentCode] = true) and ([MUADaysPresent] = true)),
#"Expanded Details" = Table.ExpandTableColumn(#"Filtered Rows1", "Details", {"A/AType"}, {"A/AType"})
in
#"Expanded Details"

Before you expand the Grouped table:

Notice your 18 days stretch from 2/11/2020 to 3/11/2020.  The actual codes are in the Table Column "Details".  We just need to expand the table column "A/AType" (The last step of the query above).

Hope that helps!

--Nate

[JanoLehocky]

Hello Nate!

Thank you so much for your reply.

My colleague and I have tried this out and have some questions. First just want to re-state the goal.

A/A means Attendance/Absence Codes. For the purpose of this report, no distinction between the different kinds of codes needs with respect to being away... or at work..etc...

These are the codes that need to be included with code MUA to be reported on

DBH
VAC
TRV
LNP

Looking to understand the >12 day consecutive entries of the above codes that do include MUA.

QUESTION

We were wondering what the purpose of the 'NonVacCode' was.

With the first column alone I was able to get the following:

I an still trying to figure out how to get the different A/A Types to show for each of the periods of more than 12 days as long as MUA is part of that group of days.

i.e.: for the 18 day stretch from Feb 11 to March 11:  17 were MUA and 1 was DBH

I may be missing something completely... Please advise.

Thank you for your time!

Jano

[watkinnc · Accepted solution]

Finally, we have an example that I can use to show us the wonderful functionality of the GroupKind.Local parameter of the Table.Group function.  Your dates are already sorted by date within the name--perfect.  We need to mark the instances of absences--add a column that checks for the values you specified, including "VAC", if true, True, else False, and let's call it AbsenceCodes.  Add another column that marks the row as one of the codes specified besides "VAC", if true, True, else False, and let's call it NonVacCode.  Now, add the Group command, grouping our new AbsenceCode column.  Add Max aggregations for your NonVacCode, and both Min and Max for A/AType Start Date, and then add and All Rows Aggregation named Details.

Now if you were doing this via the GUI, you'd get two rows--one for True and One for False.  But if we go back in right before the final parentheses, add a comma, and then GroupKind.Local, then its a whole different ballgame.  In fact, GroupKind.Local groups values only as long as they are contiguous.  As soon as the value changes, new group. Now you can filter on Count > 12, and both the AbsenceCode and NonVacCode = True.  Check out the code below, just copy and paste into the advanced editor.  Let me know how you like it.

let
Source = Excel.Workbook(File.Contents("C:\Users\xx\xx\Suncor Base Plant – Sample Data - Power BI Help Request.xlsx"), null, true),
Table_1_Table = Source{[Item="Table_1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table_1_Table,{{"Org. Text", type text}, {"WS rule", type text}, {"Name", type text}, {"A/AType", type text}, {"Pers.No.", Int64.Type}, {"Absence/Attendance hours", Int64.Type}, {"Planned working hours", Int64.Type}, {"Start Date A/A Type", type date}, {"Created on", type date}, {"Time", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "AbsentCode", each List.Contains({"MUA", "LNP", "VAC", "DBH","TRV"}, [#"A/AType"]), type text),
Custom2 = Table.AddColumn(#"Added Custom", "AbsentNonVAC", each List.Contains({"MUA", "LNP", "DBH","TRV"}, [#"A/AType"]), type text),
#"Grouped Rows" = Table.Group(Custom2, {"AbsentCode"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"NonVacDaysPresent", each List.Max([AbsentNonVAC]), type text}, {"Start Date", each List.Min([#"Start Date A/A Type"]), type nullable date}, {"End Date", each List.Max([#"Start Date A/A Type"]), type nullable date}, {"Details", each _, type table [Org. Text=nullable text, WS rule=nullable text, #"Pers.No."=nullable number, Name=nullable text, #"Start Date A/A Type"=nullable date, #"A/AType"=nullable text, #"Absence/Attendance hours"=nullable number, Planned working hours=nullable number, Created on=nullable date, Time=nullable time, AbsentCOde=text, AbsentNonVAC=text]}}, GroupKind.Local),
#"Filtered Rows2" = Table.SelectRows(#"Grouped Rows", each [Count] > 12),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows2", each ([AbsentCode] = true) and ([NonVacDaysPresent] = true))
in
#"Filtered Rows1"

--Nate