Forum Discussion

aachavez13's avatar
aachavez13
Regular Visitor
3 years ago
Solved

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...
Need Help
  • jgordon11's avatar
    jgordon11
    3 years ago

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

  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

BA_Pete's avatar
BA_Pete
Super User
3 years ago

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's avatar
aachavez13
Regular Visitor
3 years ago

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
  • jgordon11's avatar
    jgordon11
    Resolver II
    3 years ago

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

    • aachavez13's avatar
      aachavez13
      Regular Visitor
      3 years ago

      Thank you so much, that did the trick!!

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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