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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
shannon8000
Frequent Visitor

Filter to most recent employee record

Hello!

I have a dilemna.  I have employee records where each time a change is made to their profile, a new record is generated.

 

With that, I need to filter for all to return only the most recently updated record for each. Below is a generic sample of what I am working with.  I need to be able to filter out the older records from the report view.  I've searched the forums and have not found something so specfic as this ask.  Basic and advanced filtering do not have applicable options in this scenario.  Longer term employees can have hundreds of records.

EmployeeUnique IdentifierRecord Last Modified
Smith, Joexchkihs1/25/20249:35:20 AM
Smith, Joexchkihs5/1/202410:35:17 AM
Steven, Suedkslsjs12/1/20235:55:26 PM
Steven, Suedkslsjs6/1/20247:37:15 AM
1 ACCEPTED SOLUTION
ThxAlot
Super User
Super User

Simple enough,

ThxAlot_0-1717827590801.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

4 REPLIES 4
shannon8000
Frequent Visitor

Thank you all!  Each of these options are great!😍

ThxAlot
Super User
Super User

Simple enough,

ThxAlot_0-1717827590801.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Unique Identifier", type text}, {"Record Last Modified", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Count", each Table.Max(_,"Record Last Modified")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Unique Identifier", "Record Last Modified"}, {"Unique Identifier", "Record Last Modified"})
in
    #"Expanded Count"

Hope this helps.

Ashish_Mathur_0-1717816078022.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ahmedx
Super User
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Screenshot_1.png

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors