cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aachavez13
Regular Visitor

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:
12/31/2022

Revised Due Date:
2/16/2023

12/31/2022

Original Due Date: 12/31/202312/31/2023
12/15/202312/15/2023
a. 12/31/2022
b. 01/31/2023
c. 10/31/2022
d. 09/31/2022
01/31/2023
null 
2 ACCEPTED SOLUTIONS

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

View solution in original post

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

5 REPLIES 5
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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/202312/31/2023
12/15/202312/15/2023  - Error
a. 12/31/2022
b. 01/31/2023
c. 10/31/2022
d. 09/31/2022
01/31/2023

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

Thank you so much, that did the trick!!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors