Filter out old data basis timestamp column

[Anonymous]

Hello All,

this is my first query on this website.

i have a data with following details (changes in data keep occuring and hence i would like to get a final table with most update data). this all operation i would like to achieve in query editor of Power Bi

EMP ID  EMP Name    Location             Timestamp
12345    Steve                 New York          21.05.2019 11:00:00
12345    Steve                 New Jersey       21.05.2019 11:05:00
23456    Mark                 New York           21.05.2019 11:00:00
34567    Ruby                 Los Angeles       21.05.2019 11:00:00
34567    Ruby                 Ohio                      21.05.2019 11:05:00

I would like to create a final table with only below rows as my final result

EMP ID  EMP Name    Location             Timestamp
12345    Steve                 New Jersey       21.05.2019 11:05:00
23456    Mark                 New York           21.05.2019 11:00:00
34567    Ruby                 Ohio                      21.05.2019 11:05:00

[Anonymous]

Hey @Anonymous 

You need to fetch the latest date according to the unique ID

With Dax you can do something like

Latest Date = CALCULATE(
    MAX('Table'[Timestamp]),
    ALLEXCEPT('Table','Table'[EMP ID])

Where table is your table name.

Then check what is your latest date

isLatest = IF('Table'[Latest Date] = 'Table'[Timestamp],1,0)

Filter where isLatest = 1

Cheers!
A

[Anonymous]

Hello A, thank you for taking out time to reply. Since i am very new to Power BI, my question might feel silly.

i am trying to filter old data in the query editor itself so that i can bring only latest data for my future calculations. how do i write your formula inside query editor?

[Arjunarao]

Why do you want to do in Power Query.

[Anonymous]

Hello Arjunarao - Thank you for your reply.

I want to do it in query editor, since once i remove the old data rows, then the modified data set will be integrated alongwith other tables.

Current problem - I receive a new file with data only for a particular employee whose data has been changed but i cant delete the old data from the file itself. hence i want to remove the old data using query editor but at the same time preserve the data in input files.

can we put a counter which will give 1,2,3 basis the latest timestamp for each employee and reset itself to 1 for each new employee. that way, i can try to group records basis the max value for each employee