Extract date from text column

[aachavez13]

Relativly new to Power Query, needing some help on how I would go about extracting the date from a column that has multiple dates / text.  Column contains data such as below, I would like to have the custom column generate the (orange text) latest due date. 

All Due Dates	Original Due Date (new column)
Original Due Date: 12/31/2022 Revised Due Date: 2/16/2023	12/31/2022
Original Due Date: 12/31/2023	12/31/2023
12/15/2023	12/15/2023
a. 12/31/2022 b. 01/31/2023 c. 10/31/2022 d. 09/31/2022	01/31/2023
null

[jgordon11 · Accepted solution]

= Table.AddColumn(Source, "Custom", each try Date.From([All Due Dates]) otherwise Date.From(Text.Select([All Due Dates], {"/", "0".."9"})))

[BA_Pete]

Hi @aachavez13 ,

In Power Query add a new custom column like this:

Text.Select([All Due Dates], {"/", "0".."9"})

You'll then just need to change your new column to to a date data type.

Pete

[aachavez13]

Thank you BA_Pete, 

this did help however I am now running into an Error on the lines that do not contain text and only contain a date.  If possible I would also like this data to be pulled into the new column.

Due Dates	Original Due Date (new column)
Original Due Date: 12/31/2022 Revised Due Date: 2/16/2023	12/31/2022
Original Due Date: 12/31/2023	12/31/2023
12/15/2023	12/15/2023  - Error
a. 12/31/2022 b. 01/31/2023 c. 10/31/2022 d. 09/31/2022	01/31/2023

[BA_Pete · Accepted solution]

Hi @aachavez13 ,

I see you've already accepted a solution to this thread but, for future readers, I would just update my original column calculation to this:

Text.Select(Text.From([All Due Dates]), {"/", "0".."9"})

...or to explicitly output to date format to handle regional formats and two-digit years:

Date.From(
    Text.Select(
        Text.From([All Due Dates]),
        {"/", "0".."9"}
    )
)

Pete

[jgordon11 · Accepted solution]

= Table.AddColumn(Source, "Custom", each try Date.From([All Due Dates]) otherwise Date.From(Text.Select([All Due Dates], {"/", "0".."9"})))

[aachavez13]

Thank you so much, that did the trick!!