Unusual Date Format in CSV -Converts in Excel but not in PowerQuery

[IanDavies]

I have a CSV file that is sent to me weekly it has a plethora of columns but the two date columns shown below are the ones I am struggling with, You can see the in Enrollment Date and Last Check-In the date appear to be XX:XX.xx format. Ive not come across this before.

If I check the cell contents it seem to show the correct time/date in the formula bar and whats shown in the cell is a small portion of the date.

If I convert these columns to short dates in Excel they work fine as you can see here:

if I convert them back to general, I see a more traditional unformatted date:

but PowerQuery does not know how to handle them and I don't want to have to manually manage this every day.

This export has been like this forever and my predecessor just deleted the two columns, but I really need to use them do demonstrate activity.

Has anybody come across this before?

[IanDavies]

Apologes for not coming back to you. Unfortunately I only get to use PowerBI for reporting as a very small part of my job and I got dragged off to more important things.

A lot of the data I handle is quite confidential and I have to be careful uploading anything, given the fields were not essential to the report, I decided to remove them rather than struggle to fix the issue. I can no longer provide the error message.

Thanks for trying to help though, I do appreciate it. 

[Fowmy]

@IanDavies 

Did you apply Change type in Power Query to Date/Time ?

[IanDavies]

That is pretty much the whole point of the post.

I can change the column format in Excel wthout issue, but if I do the same in PowerQuery it just give me errors:

[Fowmy]

@IanDavies 

Please share the screenshot of the error in Power BI