Time in grade

Hi mbrierley, check this:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdJNCsQgDAXgqwyuC33xp9rtnKG74nJOML0/1YrDg0GzEZ98kUBynsaZxRyf7/Wql7eUA7LCrxa2vrgSXA3e5GWgRSqwrN1YI5AWtPCvLXfyaPyCosGdaFp21hjo3qok0qVU+Tu1vnupoiP/reqNddR0YL1NpvNozzpomvdEJnuCVAFPR2Z7sheAPp3YSnO+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Personal ref" = _t, #"Full name" = _t, Grade = _t, #"Effective date" = _t, #"Effective end date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Effective date", type date}, {"Effective end date", type date}}, "sk-SK"),

    fn_Dates = 
        (myTable as table)=>
        let
            // _Detail = GroupedRows{[#"Personal ref"="3"]}[All],
            _Detail = myTable,
            SortedRows = Table.Sort(_Detail,{{"Effective date", Order.Descending}}),
            GroupedRowsInner = Table.Group(SortedRows, {"Grade"}, {{"All Inner", each _, type table}, {"Effective date", each List.Min([Effective date]), type date}, {"Effective end date", each List.Max([Effective end date]), type date}}, GroupKind.Local,
                (x,y)=> Value.Compare(x[Grade], y[Grade]) ),
            ExpandedDynamic = Table.ExpandTableColumn(GroupedRowsInner, "All Inner", List.RemoveItems(Table.ColumnNames(GroupedRowsInner{0}[All Inner]), Table.ColumnNames(GroupedRowsInner)) ),
            KeptFirstRows = Table.FirstN(ExpandedDynamic,1),
            ReorderedColumns = Table.ReorderColumns(KeptFirstRows,Table.ColumnNames(_Detail)),
            RestoreTypes = Value.ReplaceType(ReorderedColumns, Value.Type(_Detail))
        in
            RestoreTypes,
    
    GroupedRows = Table.Group(ChangedType, {"Personal ref"}, {{"fn", fn_Dates, type table}}),
    ExpandedFn = Table.Combine(GroupedRows[fn])
in 
    ExpandedFn