Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 g...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    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.

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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