transform lastlogontime to date/time data type

[tonygpie]

I am attempting to transform the lastlogontime from Active Directory to Date/Time data type using the Query Editor in PowerBI Desktop.   Regardless if I select the entire column or just a single entry I just get an error.  It initially comes up as a Decimal Number data type and is in the Nanosecond format.  I can transform the same data to text or to a Whole Number but if I pick any of the Date data types it just says Error in each of the fields.   I have searched on Google as well as on this forum and have not had any luck finding a solution.

Any suggestions?

[lndnbrg]

It seems that AD gives you a date/time as number of 100 nanoseconds since a date in 1601: https://support.microsoft.com/en-us/help/555936

You could use #date(1601,1,1) + #duration(0,0,0,[value from AD in 100 nanosecs]/10000000) to convert it to a datetime in GMT.

[MarcelBeug · Accepted solution]

Or just use DateTime.FromFileTime.