Forum Discussion
Solved
Remove Unwanted text from Date Columns
Hi There, Please note that I need to remove following unwated Strings from here and changed into Date Format. In here I need to change values as > 1. First column Directly changed t...
If the date in the string is separated from other elements by a space, try adapting this example to your data:
let //sample table Source = Table.FromColumns({ {"01/04/2022", "N/A", "PROCESSED DATE: 03/11/2022"}}, type table[processed_date=any]), #"Dates Only" = Table.TransformColumns(Source,{"processed_date", (e)=> List.Sort( List.Transform( Text.Split(e," "), each try Date.From(_, "en-US") otherwise "")){0}, type date}) in #"Dates Only"Source
Result
If the date in the string is separated from other elements by a space, try adapting this example to your data:
let
//sample table
Source = Table.FromColumns({
{"01/04/2022",
"N/A",
"PROCESSED DATE: 03/11/2022"}},
type table[processed_date=any]),
#"Dates Only" = Table.TransformColumns(Source,{"processed_date", (e)=>
List.Sort(
List.Transform(
Text.Split(e," "),
each try Date.From(_, "en-US") otherwise "")){0}, type date})
in
#"Dates Only"Source
Result
Hi ronrsnfld
I have the same issue but with multiple coloumn, is there a form of loop that can be use to sort it.
Use List.Transform to convert a list of the relevant columns into a transformation_operations List. And use that in the Table.TransformColumns step in place of what is there.
Something like:
List.Transform(List_Of_Date_Columns_To_Process, (cn)=> {cn, (e)=> List.Sort( List.Transform( Text.Split(e," "), each try Date.From(_, "en-US") otherwise "")){0}, type date}))