Forum Discussion
Time in grade
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 |
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
4 Replies
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 ExpandedFnThis 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?
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
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-
