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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tonygpie
New Member

transform lastlogontime to date/time data type

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?

1 ACCEPTED SOLUTION

Or just use DateTime.FromFileTime.

 

Specializing in Power Query Formula Language (M)

View solution in original post

2 REPLIES 2
lndnbrg
Resolver III
Resolver III

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.

Or just use DateTime.FromFileTime.

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.