Replace Value Based on latest date

[eyespike1]

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

[wdx223_Daniel]

[eyespike1]

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 · Accepted solution]

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

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!

[eyespike1]

That worked! Thank you so much!

[Pragati11]

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]

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]

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