Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mbrierley
Advocate II
Advocate II

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 refFull nameGradeEffective dateEffective end date
3Test 3B101/04/202331/03/2024
3Test 3B101/11/202231/03/2023
3Test 3B101/05/202231/10/2022
3Test 3B201/04/202230/04/2022
3Test 3B201/04/202031/03/2022
3Test 3B201/04/201931/03/2020
3Test 3B231/10/201831/03/2019
3Test 3B201/04/201830/10/2018
3Test 3B201/04/201731/03/2018
3Test 3B201/04/201631/03/2017
3Test 3B201/04/201531/03/2016
3Test 3B101/04/201431/03/2015
3Test 3B101/04/201331/03/2014
3Test 3B101/08/2010

31/03/2013

3Test 3B101/09/200931/07/2010

 

Final data

 

Personal refFull nameGradeEffective dateEffective end date
3Test 3B101/05/202231/03/2024
1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hi @mbrierley, check this:

 

Result

dufoq3_0-1718893392394.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

4 REPLIES 4
dufoq3
Super User
Super User

Hi @mbrierley, check this:

 

Result

dufoq3_0-1718893392394.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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?

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

 

Function starts below _Detail step

 

dufoq3_0-1719391773817.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

BiAnalyst
Resolver I
Resolver I

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-

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.