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

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!

 

 

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community 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])

    • eyespike1's avatar
      eyespike1
      Frequent Visitor
      5 years ago

      That worked! Thank you so much!

    • 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!