Forum Discussion
Extract date from text column
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: Revised Due Date: | 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 |
= Table.AddColumn(Source, "Custom", each try Date.From([All Due Dates]) otherwise Date.From(Text.Select([All Due Dates], {"/", "0".."9"})))
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
5 Replies
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
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/2022Revised Due Date:
2/16/202312/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/202201/31/2023 = Table.AddColumn(Source, "Custom", each try Date.From([All Due Dates]) otherwise Date.From(Text.Select([All Due Dates], {"/", "0".."9"})))
