Forum Discussion

mbrierley's avatar
mbrierley
Advocate II
2 years ago
Solved

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 dis...
Need Help
Tips and Tricks
  • dufoq3's avatar
    dufoq3
    2 years ago

    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's avatar
mbrierley
Advocate II
2 years ago

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's avatar
dufoq3
Community Champion
2 years ago

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