Employee Data - Tenure in Current Role and Current Location

[JP8991]

Hello All,

I looking to create two new columns, preference would be Power Query but happy with DAX too.

The columns would be:

• Tenure in Current Role
• Tenure in Current Role and Location

I have highlighted them below to help explain what I mean.

Thanks,

[v-jingzhang · Accepted solution]

Hi @JP8991 

Create a blank query, open its Advanced Editor and clear the codes in it. Then copy and paste below codes to check the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc/BCoMwDAbgV5GchSatbvXoPHvzJj2UUZyXHmzfH20nLTjddkpIvvyQcYQWSqiZIMaR09YTYiidsX4xRa+tnswS56DKN6+YwF+8SVr8ES6jfmwdZ1we9GD08xXZPTH6DM3slhnfHZ65OrourGh/6dRV2eG3PJFccxE326lonZud19aHb9OFvAg+XhAotQI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Period End Date" = _t, #"Employee ID" = _t, #"Job Title" = _t, Tenure = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Period End Date", type date}, {"Employee ID", Int64.Type}, {"Job Title", type text}, {"Tenure", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee ID", "Job Title"}, {{"Alldata", each _, type table [Location=nullable text, Period End Date=nullable date, Employee ID=nullable number, Job Title=nullable text, Tenure=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn(Table.Sort([Alldata],{"Tenure",Order.Ascending}),"Tenure in Current Role",1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Alldata"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Location", "Period End Date", "Tenure", "Tenure in Current Role"}, {"Location", "Period End Date", "Tenure", "Tenure in Current Role"}),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Employee ID", "Location", "Job Title"}, {{"Alldata2", each _, type table [Employee ID=nullable number, Job Title=nullable text, Location=text, Period End Date=date, Tenure=number, Tenure in Current Role=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom2", each Table.AddIndexColumn(Table.Sort([Alldata2],{"Tenure",Order.Ascending}),"Tenure in Current Role and Location",1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Alldata2"}),
    #"Expanded Custom2" = Table.ExpandTableColumn(#"Removed Columns1", "Custom2", {"Period End Date", "Tenure", "Tenure in Current Role", "Tenure in Current Role and Location"}, {"Period End Date", "Tenure", "Tenure in Current Role", "Tenure in Current Role and Location"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom2",{{"Tenure", Order.Descending}})
in
    #"Sorted Rows"

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[JP8991]

@v-jingzhang wow this is amazing!

Excatly what I wanted, thank you so much.