cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
solobaz
Regular Visitor

Filter out old data basis timestamp column

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

4 REPLIES 4
Anonymous
Not applicable

Hey @solobaz 

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

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?

Why do you want to do in Power Query.

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors