Incorrect local date - Australian format

[Anonymous]

Hi,

(Power BI version - Sept 2018 desktop)

I am importing data from SAP BW. I have a date field in the source which imports as text.

it comes as dd.mm.yyyy eg for 28th August 2018 it is 28.08.2018

I cannot change the data type to "date" -  I get the message "We Can't austomatically convert the column to Date type"

So I tried using dax to create a new column with a formula to substitute the "." wiith "/" as well as the format command "dd/mm/yyyy"

I then also changed the data type to date (The default one - d/MM/yyyy). this time the data type change worked, but is now showing as american format (mm/dd/yyyy).

The only way I can fix this is to take apart my original text field and put it as mm/dd/yyyy

MID('Date],4,2) &"/"& LEFT(Date],2)&"/"&RIGHT(Date,4)

and then change the data type to Date - d/MM/yyyy.

I have got my local settings as Australia.

This sounds like a bug? Or am I missing something?

Thanks

[v-jiascu-msft]

Hi @Anonymous,

Did you try to change the option "Regional Settings"? It's used to detect the format of the data source. 

Best Regards,
Dale

[Anonymous]

Hi @v-jiascu-msft.

Thanks for the reply.

Yes, I have ensured my setting is English (Australia)

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

I found you could change the type in the Data tab. Please do it in the Query Editor that is a data washing tool. You will find it easy now. Please refer to the snapshot below.

Best Regards,
Dale

[Anonymous]

Thanks Dale.

This works.

I was changing the format in the data modelling tab.

Now I will do it in the query editor

[Anonymous]

Also from Aus.  There are a few ways to handle this and i've had some headaches with this too.  I work on some US VMs as well which throws additional spanners in the works so I certainly feel your pain.

The very first thing to you want to check is whether your dates are being stored correctly and just shown as US format, or whether they are being stored incorrectly but shown in the correct format.

For example  if your source system gave you 08.10.2018.  Did it record correctly as the 8th of October and then display as 10/08/2018, or did it store it as 10th August and then show it as 10/08/2018?  The easiest way to test that is to import the data set and then use the formatting tools to show the date in the 10-Oct-2018 type format.

First Power Query formula worth trying is Date.FromText( )  This usually does a pretty good job, especially if you format the string dates into the YYYY-MM-DD format.

You could also attempt a 'Split column by delimiter' to create 3 columns giving you the Day, Month and Year.  You could then create the new date formatted column using Date.FromtText([YEAR] & "-" & [Month] & "-" & [Day]) where the columns i mentioned are your 3 split out columns.

[Anonymous]

Thanks for the tips.

Shouldn't this be considered a bug?

My data is from BW, and it imports as text in the format of dd.mm.yyyy

I can tell it is in this format because of the date itself - eg one of them is 30.07.2015

[Anonymous]

I guess i'm just too used to fighting against it over the years.  The same problem has always cropped up in other areas too like Excel and VBA.