Forum Discussion

MarcUrdang's avatar
MarcUrdang
Post Patron
6 years ago
Solved

Text to Date format

Good morning...   I have a field which is text and has the following example:  SOP 27/01/2019   I need to extract the date part and  convert into a date field so I split the field into two:  Fiel...
Need Help
  • vivran22's avatar
    vivran22
    6 years ago

    MarcUrdang 

     

    Steps followed in Power Query

     

    Go to the Power Query Editor window

     

     

    Step 1: Home > Use First Row as Headers (in case your column header is not what it is supposed to be)

     

     

    Step 2(optional): Right Click on the column > Duplicate Column (in case you want to keep the original column)

     

     

    Step 3: Right Click on the column > Split Column > By Delimiter

     

     

    Step 4: Select Space from drop-down > Ok

     

     

    It will create two columns.

     

    Step 5: Select the Date column > Transform > Extract > Last Characters > Enter 7

     

     

     

    Step 6: Left Click on the 'ABC' icon in the column header > Select Date (It will transform it in the date column)

     

     

    Step 7:Home> Close & Apply

     

     

    Regards,
    Vivek

    If it helps, please mark it as a solution

    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

MarcUrdang's avatar
MarcUrdang
Post Patron
6 years ago

Hi

No it doesn't seem to work .. I formated your changed column to read as MMMYY and then created a new column showing the month/year of today ie Format(Today(),"MMMYY") which shows as Feb20 and then tried to compae the MMMYY from your new created field with this one and I get an error?

 

Also I am not sure of the steps to use Query to get the field you created?

vivran22's avatar
vivran22
Community Champion
6 years ago

MarcUrdang 

 

Steps followed in Power Query

 

Go to the Power Query Editor window

 

 

Step 1: Home > Use First Row as Headers (in case your column header is not what it is supposed to be)

 

 

Step 2(optional): Right Click on the column > Duplicate Column (in case you want to keep the original column)

 

 

Step 3: Right Click on the column > Split Column > By Delimiter

 

 

Step 4: Select Space from drop-down > Ok

 

 

It will create two columns.

 

Step 5: Select the Date column > Transform > Extract > Last Characters > Enter 7

 

 

 

Step 6: Left Click on the 'ABC' icon in the column header > Select Date (It will transform it in the date column)

 

 

Step 7:Home> Close & Apply

 

 

Regards,
Vivek

If it helps, please mark it as a solution

Kudos would be a cherry on the top 🙂

https://www.vivran.in/