Forum Discussion
Replace Value Based on latest date
Hi, I'm looking to replace values based off of the date column.
For each Name, I want the location to be updated based off of the latest Date.
This is the starting data:
This is the result:
Working in Excel Power Query
=Table.Combine(Table.Group(Source,"Name",{"n",each let NewLocation=Table.First(Table.Sort(_,{"Date",1}))[Location] in Table.ReplaceValue(_,null,NewLocation,(x,y,z)=>z,{"Location"})})[n])
8 Replies
Hi eyespike1 ,
I have considered following input data: (just added an Index column for clarity)
Then I created a calculated column as follows using DAX:
newLocationcol =CALCULATE(MAX('Table'[Location]), FILTER('Table', 'Table'[Date] = MAX('Table'[Date])))Now added this column to the table visual and you get the respective output:So, basically a new column calculation is created.
Thanks,
Pragati
Hi Pragati,
Thank you for your reply. I tried your solution but since this is in Excel and not PowerBi I don't think it will work.
I also didn't clearly state that I would have multiple "Names".
Somebody's locations would be Location2
Somebody2's locations would be Location6
Somebody3's locations would be Location13
Thanks!
Seth
=Table.Combine(Table.Group(Source,"Name",{"n",each let NewLocation=Table.First(Table.Sort(_,{"Date",1}))[Location] in Table.ReplaceValue(_,null,NewLocation,(x,y,z)=>z,{"Location"})})[n])
Daniel,
Thanks for your reply. You code worked, unfortunately I didn't clearly state that I would have multiple "Names".
Is it possible for you to modify so we are getting the most recent location by date for each name?
Somebody's locations would be Location2
Somebody2's locations would be Location6
Somebody3's locations would be Location13
Thank you!
=Table.Combine(Table.Group(Source,"Name",{"n",each let NewLocation=Table.First(Table.Sort(_,{"Date",1}))[Location] in Table.ReplaceValue(_,null,NewLocation,(x,y,z)=>z,{"Location"})})[n])
