Forum Discussion
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:
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,
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)However, I fixed the year start from 2000, do you have any years before 2000? If so, then you need to modify the DAX.
4 Replies
- mahoneypat
Microsoft 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
ryan_mayuSuper 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)However, I fixed the year start from 2000, do you have any years before 2000? If so, then you need to modify the DAX.
- amitchandak
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))))
