Grouping Date sets within a month

Hi johnlhaase - See if this works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdOxDYMwGEThXVwj2b7f4KTNAhkAsQL7l1iCQ1TJkyy78Nc93bqmmmtWUUlTij6P+9NbG88499c2/XGCLqBr0M3QLdB16F7QvaGrhUJQ5LufFEQxBV1MQRpTUMcUBDIFjUxBJlNQ6qICrUx5LfFa4rXEa4nXEq8luijRSYluKnineHTSL6rMpbAMLJ1zOwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start of Month" = _t, EmpID = _t, Fleet = _t, OnOff = _t, #"Rotation Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start of Month", type date}, {"Rotation Date", type date}}),
    FakeNullRow =
        {null} &
        List.RemoveLastN(
            Table.Column(
                Source, "OnOff"
                ),
                1
            ),
    CombinedWithNullColumn = Table.ToColumns(#"Changed Type") & {FakeNullRow},
    BackToTable = Table.FromColumns(CombinedWithNullColumn, Table.ColumnNames(#"Changed Type") & {"Previous OnOff Status"}),
    #"Added Changed Status" = Table.AddColumn(BackToTable, "Changed Status", each if [Previous OnOff Status] = null then null else if [OnOff] = [Previous OnOff Status] then null else "Changed"),
    #"Added Index" = Table.AddIndexColumn(#"Added Changed Status", "Index", 0, 1, Int64.Type),
    #"Added Grouping" = Table.AddColumn(#"Added Index", "Grouping", each if [Changed Status] = "Changed" then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Grouping",{"Grouping"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Grouping"}, {{"AllRows", each _, type table [Start of Month=nullable date, EmpID=nullable text, Fleet=nullable text, OnOff=nullable text, Rotation Date=nullable date, Previous OnOff Status=nullable text, Changed Status=nullable text, Index=number, Grouping=nullable number]}}),
    #"Added Min Date" = Table.AddColumn(#"Grouped Rows", "Min Date", each Table.Min([AllRows], "Rotation Date")[Rotation Date]),
    #"Added Max Date" = Table.AddColumn(#"Added Min Date", "Max Date", each Table.Max([AllRows], "Rotation Date")[Rotation Date]),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Added Max Date", "AllRows", {"Start of Month", "EmpID", "Fleet", "OnOff", "Rotation Date"}, {"Start of Month", "EmpID", "Fleet", "OnOff", "Rotation Date"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded AllRows",{"Start of Month", "EmpID", "Fleet", "OnOff", "Rotation Date", "Min Date", "Max Date"})
in
    #"Removed Other Columns"

It turns this:

into this:

I did it by using some cool techniques from ImkeF at this blog article, but instead of using a custom function, I just did it in the code.

1. It takes your OnOff column and adds it to the end of the current table as a new column, but shifted by one row. 
2. Adds a "Changed" text field to see if there is a change from on to blank (off I presume.)
3. Adds an index. I just needed a sequence of numbers.
4. If it is "Changed" give me the number in the Index column, otherwise, give me a null
5. I then filled this column down. Now at every change, I have a different number.
6. Grouped by that number, then used Table.Min() and Table.Max() to extract the min/max dates, then re-expanded your original columns.

Everything is UI driven (or in the Add Custom Column box) except for three steps:

• FakeNullRow
• CombinedWithNullColumn
• BackToTable

You can see what each step is doing though by looking at it as you walk through it.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.