Sorting by most recent date

[ostur]

I really hope you can help me, and save me from a major headache.

My problem is that I have a list of names, grouped into several categories, but the way my system works is that every change to the directory results in a change only from that date. 

f.ex. if Name should be moved from City A to City B, and the change is made on 1 Jan, the new information will only work from 1 Jan, but all data prior to 1 Jan will show John in City A.

I am hoping I can:

1. Take the list I have and group the information so that I have one name, all groups, but only the most recent date of each grouping

   Name	City A	Jan 1
   Name	City B	Most recent date

2. Filter the most recent date by name, and remove all groupings before that date
3. Ending up with something like this

Does that make any sense? Please let me know if you have any questions.

[JoeBarry · Accepted solution]

Hi @ostur 

This informative video will help filter by the latest date https://www.youtube.com/watch?v=hidJ5T_DYQ0 

In Power Query 

• highlight the Name column and in the ribbon, click on Group by
• Add a new Column Name, lets say MaxDate, In Operation choose Max and Choose in Column the Date Column
• Add another aggrigation and name it All. Operation should be All
• Press OK
• Expand the new column and untick Name as it's already in the table
• In the MaxDate column filter the date to today
• Then in the measure change todays date to the date column 

=[Date]​

• This will show the last entry and it is dynamic, if there changes in the future, then these will be implemented.

Thanks

Joe

If this post helps, then please Accept it as the solution 

[ostur]

Thank you so much Joe, you're a lifesaver!