Remove Unwanted text from Date Columns

[Rifdhy]

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

[ronrsnfld · Accepted solution]

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

[Dee2024]

Hi ronrsnfld 

I have the same issue but with multiple coloumn, is there a form of loop that can be use to sort it.

[ronrsnfld]

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

[serpiva64]

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 !