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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

convert number to date

Hi,

I have a date column I'm trying to import that is stored in the CSV file as numbers. I've tried converting the column to date but it doesn't recognise the date format I'm trying to use so it gives me all sorts of weird dates as a result. The dates are basically dd/mm/yy without the / seperator as below:

IDKFA_0-1594603834096.png

 

Any one know how I can transform this data into date data within power query? Do I need to make the changes after the data is loaded?

 

Thanks in advance.

 

Kind Regards,

 

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@Anonymous 

please try to create a column

Column = 
VAR formatchange="0"&'Sheet24'[date]
VAR _newdate=if(LEN('Sheet24'[date])=5,formatchange,'Sheet24'[date])
VAR _day=LEFT(_newdate,2)
Var _month=mid(_newdate,3,2)
VAR _year="20"&RIGHT(_newdate,2)
return date(_year,_month,_day)

1.PNG

However, I fixed the year start from 2000, do you have any years before 2000? If so, then you need to modify the DAX.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
mahoneypat
Employee
Employee

You can also just highlight your column in the query editor, and use the Column From Examples features.  You just type in the desired format (mm/dd/yyyy) and you will get a custom column auto created with a formula like this

 

=Text.Combine({Text.Reverse(Text.Middle(Text.Reverse(Text.From([Column1], "en-US")), 2, 1)), "/", Text.Reverse(Text.Middle(Text.Reverse(Text.From([Column1], "en-US")), 4)), "/", Text.End(Text.From([Column1], "en-US"), 2)})

 

Please see this link for a demo from Guy in a cube - https://www.youtube.com/watch?v=GUwtPIKtqO0

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


amitchandak
Super User
Super User

@Anonymous , Try like

new Date=
var _1 = [Installationdate]& ""
var _2 =len(_1)
return
if(_2 =6 , date(year(right(_1,2)),year(mid(_1,3,2)),year(left(_1,2))),date(year(right(_1,2)),year(mid(_1,2,2)),year(left(_1,1))))
ryan_mayu
Super User
Super User

@Anonymous 

please try to create a column

Column = 
VAR formatchange="0"&'Sheet24'[date]
VAR _newdate=if(LEN('Sheet24'[date])=5,formatchange,'Sheet24'[date])
VAR _day=LEFT(_newdate,2)
Var _month=mid(_newdate,3,2)
VAR _year="20"&RIGHT(_newdate,2)
return date(_year,_month,_day)

1.PNG

However, I fixed the year start from 2000, do you have any years before 2000? If so, then you need to modify the DAX.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thank Ryan, this worked perfectly after I swapped in my column names!

Amit I didn't test your answer as Ryan's came in first sorry. Thank you for responding though! I'm sure I'll have another question for you this week 🙂

Kind Regards,

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.