NOOB - Beginning Power BI Book DateTime Error

[JuSlaughter]

Hi

First post so please be gentle.

I am looking into Power BI after using Excel and Reporting Services for quite a few years. I have purchased the Beginning Power BI book and am working through it with no issues......until I get to Chapter 9.

I have imported a provided CSV file. It opens OK and one of the instructions is to make sure the 3 Date columns are actually set to DateTime. They aren't, they are text. So I selected Data Type Date\Time on one of the columns but it returns errors for most of the values.

The dates are formatted as 09/18/2017 08:00:55 AM or 09/10/2017 02:38:59 PM. It appears that the errors only occur on the AM date times.

Any suggestions on how I get round this issue, bearing in mind I only started looking at this today ;o)

Thanks

Ju

[JuSlaughter]

I'm going to try Dropbox

https://www.dropbox.com/s/ztkrt4l9spj0nl5/SF311Calls.csv?dl=0

[v-shex-msft]

Hi @JuSlaughter,

I test on your data but can't reproduce the issue which you mentioned. I think the issue is related to the date format.(for instance: UK date and US date)

I'd like to suggest you to add a custom column to transfer these date to your locale date format, it may work through your issue.

Sample:

 #"Added Custom" = Table.AddColumn(#"Changed Type", "Opened(US)", each DateTime.FromText(Text.From([Opened]),"us"))
 #"Added Custom" = Table.AddColumn(#"Changed Type", "Opened(UK)", each DateTime.FromText(Text.From([Opened]),"uk"))

Regards,

Xiaoxin Sheng

[JuSlaughter]

Sorry for the delay, been stuck doing the day job and now I'm on holiday for a few weeks. Will have a look when I get back and let you know.

Thanks for your help.

Cheers

[JuSlaughter]

Hi

Thanks for the response. I will give it a try although I dont fully understand at this stage of my learning where the code you have supplied should go but I will try and figure it out.

Thanks

[JuSlaughter]

Morning, what is the best way to link a file on this forum?

Thanks

[v-shex-msft]

Hi @JuSlaughter,

Please share sample csv file to test, it will be help for troubleshooting.

Regards,

Xiaoxin Sheng

[Greg_Deckler]

Hmm, that's odd. I created a CSV file with those values in it and was able to successfully convert it to Date/Time data type. Can you supply a link to the CSV file?