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