Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NickFel
Frequent Visitor

Convert column from Text to Date

Ok I have a column in my data that has the year and month of an item captured.

The issue is that it is a text field that lists it as text in this format:

YYMM

 

I know I could split these and recombine them in the correct order and format it as date but that would take several steps.

 

Is there a more efficient way to perform this funtion?

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

Are all the years in the 2000s ?

 

you may have to modify the above if your column is only the last 2 year digits - YYMM

 

Column = DATE("20"&LEFT([Test],2), RIGHT([Test],2), 1)

 

YYMM 2 DATE.png 

View solution in original post

5 REPLIES 5
SqlJason
Memorable Member
Memorable Member

If the column is called [Test] and if the format is YYYYMM, I generally do

 

= DATE(LEFT([Test],4), RIGHT([Test],2),1)

 

 Careful about the year though. In your case, you only have YY instead of YYYY, so might have to concatenate 19 or 20 based on what you want.

Sean
Community Champion
Community Champion

Are all the years in the 2000s ?

 

you may have to modify the above if your column is only the last 2 year digits - YYMM

 

Column = DATE("20"&LEFT([Test],2), RIGHT([Test],2), 1)

 

YYMM 2 DATE.png 

NickFel
Frequent Visitor

Thanks this woked

 

Interesting I'm getting an error that the name date wasn't recognized?

Forgot to tell you,  I did this in DAX (the modelling side) and not in M (query editor side)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors