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

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

K_L_G's avatar
K_L_G
Regular Visitor
3 years ago

Hello, thank you for the very helpful formula! I have the exact same issue as eyespike1, and this forum has been a blessing.

 

One stumbling block I have encountered: Are you able to advise on excluding nulls as the most recent value? Example: 

 

In this scenario, I want:

Somebody's locations would be Location3

Somebody2's locations would be Location6

Somebody3's locations would be Location12

 

Edit: The only option I have found is adding a helper column that tags rows w/ number in "Location" as "1" and nulls as "2". Then modifying the Table.Sort step to sort by both the helper column and Date column. Is the the only / best way?

 

thank you!