Forum Discussion

eyespike1's avatar
eyespike1
Frequent Visitor
5 years ago
Solved

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: ...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

    =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])

eyespike1's avatar
eyespike1
Frequent Visitor
5 years ago

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

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
5 years ago
=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])