How to convert date/time stamp into date and time ?

[bourne2000]

Hi

I am having an excel file that has date/time column as below format

When I tried to import into Power BI, I get the wrong time. 

For example for the above time, it should be 16:56 pm, instead, it shows 3.36.19 am

Can anyone advise me? My data is in a CSV file.

https://we.tl/t-t4zhnBzxmM

[v-yanjiang-msft · Accepted solution]

Hi @bourne2000 ,

According to your description, here's my solution.

1.Select Get Data>CSV.

When connect to your sample data, the following screen will pop up automatically.

Click the Delimiter bar, many options will appear, select Custom.

Then enter a dot in the box, 

Get the correct result.

Best Regards,
Community Support Team _ kalyj

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

[bcdobbs]

So are you getting the right number of rows but it's shuffling the timezone:

Are you able to import the csv into Power Bi your end and share the pbix? 

(Off to bed now but will look in morning!)

[bcdobbs]

Hi @bourne2000 ,

I can't recreate the issue you're seeing with the file exactly.

However when I go to import it Power Bi mistakenly thinks the file is colon separated. Suspect it's not the issue but just to check have you switched it back to comma:

[bourne2000]

@bcdobbs  It's comma only

[bcdobbs]

The only other thing I can think of is whether something is happening with the timezone at the end (+000). Long shot but try selecting the original "Changed Type" step and changing the column to Date/Time/Timezone

[bourne2000]

@bcdobbs  When I import the csv file into power bi with comma operator, MY date table like below

Then I changed the data type into date/time/Timezone

When I extracted hours from this table, I am not able to see the complete hours. I checked the csv file, it has hours from 1 pm to 4 pm, but that was not captured. 

[bcdobbs]

Is the data also missing once in power bi? Just trying to rule things out as running out of ideas I'm

afraid.

[bourne2000]

@bcdobbs Power BI conisder 13.00 pm into 3.00 am. I think some timezone is not recognized.