Forum Discussion
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 to Date Format
2. Second Row shows as BLANK
3. in third row that String get deleted and Changed to Date Format
Please help me to do above task guys
Thanks in Advance,
Faithfully
Rifdhy
@TexttoDate@RemoveStringsfromDateColumns
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
4 Replies
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}))
Hi,
you can extract last 10 characters
then change type to date
and finally replace errors with null
null is blank in visualization
If this post is useful to help you to solve your issue consider giving the post a thumbs up
and accepting it as a solution !
