Query Editor-Replace Errors in Date column with latest date

[Wojcik_J]

Hello,

i have a file where in one of the columns i have dates or text value "History".

What i want to do in the query editor is to replace the "History" with latest date in the column.

What i tried was :

1. Convert the column to "Date" datatype which result with the "History" cells changing to Error

2. Replacing Errors with latest value from the column.

I looked into how filtering latest date transform to code in advanced editor and came up with below:

 #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", let latest = List.Max(#"Changed Type"[Date when data was loaded to database]) in each {{"Date",latest}})

/

#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", let latest = List.Max(#"Changed Type"[Date when data was loaded to database]) in each Date.From(latest))

But they are giving error :

Can you help me with correcting it ? 

Below example dataset :

[Greg_Deckler]

@ImkeF 

[ImkeF · Accepted solution]

Hi @Wojcik_J ,

I would write it like so:

Table.ReplaceErrorValues(#"Changed Type", {{"Date", List.Max(Table.RemoveRowsWithErrors(#"Changed Type", {"Date"})[Date])}})

[Wojcik_J]

Works, thank you for your support !