Grouping consecutive dates into date ranges for sick leave

[vanschex]

I would really appreaciate any assistance in sloving my problem please. This is my current data table structure in Power BI:

Employee	DATE	Shift Type
A	1/01/2018	Sick
A	2/01/2018	Rostered off
A	3/01/2018	Rostered off
A	4/01/2018	Sick
A	5/01/2018	Work
A	6/01/2018	Work
A	7/01/2018	Sick
A	8/01/2018	Work
A	9/01/2018	Sick
A	10/01/2018	Sick
A	11/01/2018	Sick

I want to determine number of continuous occurrences of sick leave but if rostered off and they have sick leave either side, then that is one occurrence. Expected result would be:

Employee	DATE FROM	DATE UNTIL
A	1/01/2018	4/01/2018
A	7/01/2018	7/01/2018
A	9/01/2018	11/01/2018

Many thanks!

[OwenAuger]

@vanschex

Here's an example of how you could transform the data in Power Query.

Paste this into a blank query to see how it works.

The main steps are:

1. Sort by Employee & Date
2. Remove Rostered Off rows
3. Group by Shift Type using GroupKind.Local, so each block of Sick/Work is grouped together, and add columns for the min/max date from each group
4. Keep only Sick groups

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUNzDUNzIwtACygzOTs5VidSASRkgSQfnFJalFqSkK+WlpcAXGhBSY4DLaFEkiPL8IIWGGS8Icl1EWuHRY4tJhaIBTBjMsYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Employee = _t, DATE = _t, #"Shift Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"DATE", type date}, {"Shift Type", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Shift Type] <> "Rostered off")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Employee", Order.Ascending}, {"DATE", Order.Ascending}}),
    Buffer = Table.Buffer(#"Sorted Rows"),
    #"Grouped Rows" = Table.Group(Buffer, {"Employee", "Shift Type"}, {{"DATE FROM", each List.Min([DATE]), type date}, {"DATE UNTIL", each List.Max([DATE]), type date}},GroupKind.Local),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows", each ([Shift Type] = "Sick")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Shift Type"})
in
    #"Removed Columns"

[vanschex]

Thanks so much Owen Auger! This achieves exactly what I'm after and a very elegant solution if my source file matches the sample file supplied. Unfortunately I am unable to make this work in my model as the determination of "Shift Type" is performed via calculated columns within a table which are not visible to a query created that references my source Power BI table. Is there an equivalent DAX solution that would achieve the same result?