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

Pragati11's avatar
Pragati11
Super User
5 years ago

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

 

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