Text to Date format

[MarcUrdang]

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

[vivran22]

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]

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 · Accepted solution]

@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/

[amitchandak]

You can use Power Query or can try a new column using dax like

New date =date(year(right(table[sopdate],4)),month(mid(table[sopdate],8,2)),day(mid(table[sopdate],5,2)))

In case it gives some issue check what you get from each one of right and mid and correct the position

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin