Forum Discussion
Need Help with Importing CSV with Strange Date Format
I know nothing of Intune, however…
According to Intune Support team here: https://techcommunity.microsoft.com/blog/intunecustomersuccess/support-tip-changes-in-all-devices-list-and-reporting-in-intune/1220946
they say:
- We’ve received reports of the last check-in datetime not being understandable in Excel. The .csv export has a space between the date and time instead of a comma and is now shown in UTC format. The .csv format for the Last Check-in is “YYYY-MM-DD HH:MM:SS.sssssss”, for example "2020-03-08 17:10:10.4438071". In Excel, you can change the column cell format to display Date and time in the format you desire.
So, when I pasted:
2020-03-08 17:10:10.4438071
into a cell in Excel, it showed like this:
where the formula bar shows a more complete version than the cell does. Excel has also 'helpfully' changed the format of the cell to:
if I change it to:
I get this:
where there is more information visible in the cell than in the formula bar!
My first question is are you getting the data into Excel before getting it into Power Query/Power BI?
If so, don't. Get the data directly from the original Intune csv file into Power Query/BI. Power Query correctly auto detects that field's type:
It looks like you've edited this file; how have you done that?
I had to edit the sensitive data out of the CSV. I am unfortunately unable to export a doc without sensitive info from my organization. Perhaps there is another way to go about this?
