convert number to date

[Anonymous]

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,

[mahoneypat]

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

[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))))

[ryan_mayu · Accepted solution]

@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.

[Anonymous]

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,