Date - Wrong year displayed (formatting problem)

[Mengerdahl]

Second formatting problem I cant solve today means second thread here today.. As earlier I seem to get stuck on getting the right format for my columns of the dataset. Im using Swedish settings on my PC if that matters for the date format. Just started working with PowerBI so im really just trying to understand the basics. 

Im using this dataset from Kaggle with orderdata. It shows orderdata from 2010 and 2011. An example of a date in the set is 04/01/10 00:00:00. where 10 is year. Not sure if 04/01 is mm/dd or dd/mm. 

When I add this file to PBI it shows as this:

It correctly identify the column as date but the format is all wrong. It identifies 04 as year and adds 20 for the year 2004 where its suppose to mean 2010. 

I have tried chaning to Locale US and it shows like this. Not much better..

Im super confused about this and different formats in general (more and more after today..) 

Does anybody have an idea what to do and if there are any settings I should do to prevent this from happening?

[Mengerdahl]

So im still having this problem with several different datasets. I just tried to use this dataset

which only lists YYYY in the year column. Shouldn´t be a problem, right? Wrong! 😞 

 

 

 

 

[ppm1]

One way to do it would be to keep it as text at first, then do a Text Before Delimiter step (transform tab, extract pull down) with a space as the delimiter. This will remove the 00:00:00 and you should be able to just convert it to Date type from there.

Pat

[Mengerdahl]

Thank you. It remove the 00:00:00 but still formats the year wrong. 

[az38]

Hi @Mengerdahl 

where exactly did you try to change local?

There are 2 places - Global and Current File local settings

With current file local settings ENglish (United States)  04/01/10 00:00:00 was recognized as 2010 year for me

[Mengerdahl]

Hi!

Good question 😉 I tried to change this in Power query when loading the file. Not sure if this is the right way then?