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?

I feel like even if I need to write some dax to substitute the "." for "/" I should then be able to change the data type to date, and for that to convert to australian date if I have my local settings set to Australia

Thanks

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

There is a easiest way to change text column to date, you can set the Regional Setting in Power BI desktop as English (Australia), save the change and reopen the report. Go to Query Editor, select the date column, change it to Date type. 

By the way, there is a October Power BI desktop released, you can update your desktop to this version 2.63.3272.40262. 

Best Regards,
Qiuyun Yu 

[v-qiuyu-msft]

Hi @Anonymous,

As the issue is resolved now, I will close this thread. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Thank you. This works now

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

You can insert a step to replace value # as blank before you change data type to date. Please select the date column, then click Replace Values, type below: 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft

Thanks for this.

some of the rows have a blank date in the database (which comes through as a "#")

Here is the error I receive.

"DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
    # "

How do I parse the # into a blank?

Cheers

Trev

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

There is a easiest way to change text column to date, you can set the Regional Setting in Power BI desktop as English (Australia), save the change and reopen the report. Go to Query Editor, select the date column, change it to Date type. 

By the way, there is a October Power BI desktop released, you can update your desktop to this version 2.63.3272.40262. 

Best Regards,
Qiuyun Yu