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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pvural
Advocate I
Advocate I

Converting Text to Year format "YYYY"

Hello everyone

 

I tried a few solutions here to convert text to date with no avail.  Here is my problem:

 

My report in excel comes with an "accounting period" column as text, example 201701. When I seperate columns 2017 and 01, and apply "sameperiodlastyear" using the year column, it doesn't recognize it as date, so formula comes back with error. When I changed the column to date 2017 becomes a weird mm/dd/yyyy format, something like November 15, 2050.

 

Basically what I am trying to do is to build the sameperiodlastyear for YOY calculations, but couldn't pass this little step to properly identify the "year" in the columns.

 

Thank you,

Petek

 

 

2 ACCEPTED SOLUTIONS
Sean
Community Champion
Community Champion

@pvural

You need a Date Column

Use this formula in the Add Custom Column step shown below to transform your Period Column to a Date Column

 = Date.FromText ( [Period.2] & "/01/" & [Period.1] )

After you split the column convert to text

Add the Custom Column - Convert it to Date and remove old column if you wish

You can then use this Date column Smiley Happy

Query Editor - YYYYMM to Date.gif

 

View solution in original post

Sean
Community Champion
Community Champion

Once you have the Date Column you can get the Year and Month like this Smiley Happy

 

Query Editor - YYYYMM to Date2.gif

View solution in original post

5 REPLIES 5
Sean
Community Champion
Community Champion

@pvural

You need a Date Column

Use this formula in the Add Custom Column step shown below to transform your Period Column to a Date Column

 = Date.FromText ( [Period.2] & "/01/" & [Period.1] )

After you split the column convert to text

Add the Custom Column - Convert it to Date and remove old column if you wish

You can then use this Date column Smiley Happy

Query Editor - YYYYMM to Date.gif

 

Thanks Sean.

 

The challenge is after splitting the columns, to only convert 2017 (Period 1) to a date format, not to bring month and year back to create a mm/dd/yyyy format... I just want to make sure Period 1 after the split will be recognized as a year as oppsed to a number or text so that i can apply sameperiodlastyear.

 

Having said that I can still use this solution, and will flag it as one. Thanks again!

 

Cheers,

PVural

Sean
Community Champion
Community Champion

Once you have the Date Column you can get the Year and Month like this Smiley Happy

 

Query Editor - YYYYMM to Date2.gif

Thank you ... Lots to learn 🙂

Sean
Community Champion
Community Champion

@pvural

BTW I just noticed you need to reverse my formula above to be like this (I'll change it too)

Period.2 first which is the Month then 1 and then the year - then you'll get the different months

= Date.FromText ( [Period.2] & "/01/" & [Period.1] )

 Query Editor - YYYYMM to Date3.png

Hope this helps! Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.