Time in grade

[mbrierley]

I'm trying to create a report where we have a line for each employee showing the first date they entered their current grade and the most recent end date (although this might be blank). I want to discount any previous periods in their current grade, where they might have had a temporary promotion in between. I'm only interested in the most latest period they have occupied their current grade. 

I can attach an example spreadsheet with the raw data on one tab and the final data, how I'd like it to look, on the other.

Ideally, I'd like to know what steps I have to do to do this in PowerQuery. 

I figure it will involve Index columns and grouping by person reference number. My previous attempts at doing this have not worked as I find it hard to distinguish between two different period at the same grade. For example, if I've been in the B2 grade from 01/01/2024 to present and previosuly from 01/01/2023 to 30/06/2023, I want to discount this latter period. But when grouping the data by person reference, both periods are lumped together, which I don't want. 

Raw Data

Personal ref	Full name	Grade	Effective date	Effective end date
3	Test 3	B1	01/04/2023	31/03/2024
3	Test 3	B1	01/11/2022	31/03/2023
3	Test 3	B1	01/05/2022	31/10/2022
3	Test 3	B2	01/04/2022	30/04/2022
3	Test 3	B2	01/04/2020	31/03/2022
3	Test 3	B2	01/04/2019	31/03/2020
3	Test 3	B2	31/10/2018	31/03/2019
3	Test 3	B2	01/04/2018	30/10/2018
3	Test 3	B2	01/04/2017	31/03/2018
3	Test 3	B2	01/04/2016	31/03/2017
3	Test 3	B2	01/04/2015	31/03/2016
3	Test 3	B1	01/04/2014	31/03/2015
3	Test 3	B1	01/04/2013	31/03/2014
3	Test 3	B1	01/08/2010	31/03/2013
3	Test 3	B1	01/09/2009	31/07/2010

Final data

Personal ref	Full name	Grade	Effective date	Effective end date
3	Test 3	B1	01/05/2022	31/03/2024

[dufoq3 · Accepted solution]

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

[mbrierley]

This appears to work 🙂 but I'm struggling to break down what you've done. Would you be able to describe how you're done this?

[dufoq3]

Hi I created function, but you can see here what is every single step of such function doing:

Function starts below _Detail step

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"),  
    GroupedRows = Table.Group(ChangedType, {"Personal ref"}, {{"All", each _, type table}}),
 _Detail = GroupedRows{[#"Personal ref"="3"]}[All],
    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

[BiAnalyst]

What you looking for can be achived by using group by whith advanced grouping using first three columns. and Table.column to ungroup them at the end. Curbal's solution has always worked for me with little modification.

<iframe width="560" height="315" src="https://www.youtube.com/embed/QaodJFeX49k?si=ahEvi4Wpa2beaoWW" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>

Video link incase embeded link doest work : https://youtu.be/QaodJFeX49k?si=6RwkXazO4Me2Wx5-