How to convert dates from data source Active Directory 1.32242E+17 to dd/mm/yyyy

[Anonymous]

Hi

I've been asked to create an AD report. But when I import the following field; accountexpires, lastlogon, lastlogonTimestamp, pwdLastSet, they appear in a format such as 1.32242E+17.

How do I convert them to dd/mm/yyyy?

Thanks in advance

Martin

[Matt-Butler]

A better solution is to just change the format to date / time. 

[v-zhenbw-msft]

Hi @Anonymous ,

In the data source, is the type a Date/Time format or scientific notation?

In Power Query Editor, please delete the change Type, then what is the result of the column? Is it a yyyy/mm/dd hh:mm:ss type?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @v-zhenbw-msft 

Thank you for your response.

I solved this by using this formula:

Last Log In Date = COALESCE(DIVIDE ( [user.lastLogon], 8.64 * 10 ^ 11 ) - 109205,0)

Thanks

Martin