cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sabeensp Member
Member

Convert whoel number to Date

Hello I have a column with Whole number like 20190106, when I try to convert that to Date it give me an error. How can I covert above whole number to YYYY/MM/DD format?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
tejaswidmello Established Member
Established Member

Re: Convert whoel number to Date

Hi @sabeensp 

 

Did you try adding M code like this?

 

Capture 4.PNG

 

 

 

Or you can also try this new column:

Column = Date(Left([data],4),Right(left([data],6),2),right([data],2)

and then change the data type of new column to Date

 

Let me know if thsi works.

 

Thanks,

Tejaswi

View solution in original post

5 REPLIES 5
tejaswidmello Established Member
Established Member

Re: Convert whoel number to Date

Hi @sabeensp ,

 

Take a look at this post. The issue is already resolved in this post.

 

https://community.powerbi.com/t5/Desktop/date-from-yyyymmdd-stored-as-whole-number/td-p/92689

 

Thanks,

Tejaswi

sabeensp Member
Member

Re: Convert whoel number to Date

@tejaswidmello  M code not working for me somehow, when I add a new calcualted column. Is there another way?

tejaswidmello Established Member
Established Member

Re: Convert whoel number to Date

Hi @sabeensp ,

 

There are two Solutions in this post.

 

Did you try the second one. Hope that works for you.

 

Thanks,

Tejaswi

sabeensp Member
Member

Re: Convert whoel number to Date

@tejaswidmello  problem with the 2nd solution is that I have over 15 Date columns, divide them into YY, MM, DD would result in 45 columns. I was hoping for some DAX formula to convert

tejaswidmello Established Member
Established Member

Re: Convert whoel number to Date

Hi @sabeensp 

 

Did you try adding M code like this?

 

Capture 4.PNG

 

 

 

Or you can also try this new column:

Column = Date(Left([data],4),Right(left([data],6),2),right([data],2)

and then change the data type of new column to Date

 

Let me know if thsi works.

 

Thanks,

Tejaswi

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)