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:  Field 1 = SOP , Field 2 = 27/01/2019

 

I need to convert the second field into a date field ending up with month/year:   Jan 2019

 

thanks

Marc

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/

4 Replies

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    Good morning MarcUrdang 

     

    I have used the Power Query to get the desired results:

     

     

    You may find the pbix file here

     

    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/