Filter out old data basis timestamp column

Question

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 BiEMP ID&nbsp;&nbsp;EMP Name&nbsp;&nbsp;&nbsp; Location&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Timestamp12345&nbsp;&nbsp;&nbsp; Steve&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; New York&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21.05.2019 11:00:0012345&nbsp;&nbsp;&nbsp; Steve&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; New Jersey&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;21.05.2019 11:05:0023456&nbsp;&nbsp;&nbsp; Mark&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;New York&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21.05.2019 11:00:0034567&nbsp;&nbsp;&nbsp; Ruby&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Los Angeles&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21.05.2019 11:00:0034567&nbsp;&nbsp;&nbsp; Ruby&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ohio&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21.05.2019 11:05:00&nbsp;I would like to create a final table with only below rows as my final resultEMP ID&nbsp;&nbsp;EMP Name&nbsp;&nbsp;&nbsp; Location&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Timestamp12345&nbsp;&nbsp;&nbsp; Steve&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; New Jersey&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;21.05.2019 11:05:0023456&nbsp;&nbsp;&nbsp; Mark&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;New York&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21.05.2019 11:00:0034567&nbsp;&nbsp;&nbsp; Ruby&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ohio&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21.05.2019 11:05:00

anonymous · Answer

Hey&nbsp;Anonymous&nbsp;You need to fetch the latest date according to the unique IDWith Dax you can do something likeLatest Date = CALCULATE(
    MAX('Table'[Timestamp]),
    ALLEXCEPT('Table','Table'[EMP ID])Where table is your table name.&nbsp;Then check what is your latest dateisLatest = IF('Table'[Latest Date] = 'Table'[Timestamp],1,0)Filter where isLatest = 1&nbsp;Cheers!A

anonymous · Answer

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 · Answer

Why do you want to do in Power Query.

anonymous · Answer

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 -&nbsp;I receive a new file with data only for a particular&nbsp;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.&nbsp;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

Forum Discussion

Filter out old data basis timestamp column

4 Replies

Related content

Slicer for filtering several columns

Timestamp Column

datetime2 shows timestamp

REAL TIME TILES AUTO CONVERT TIMESTAMP TO UTC

How to Convert String time column to timestamp or DATE DATATYPE using