Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Incorrect local date - Australian format

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

2 ACCEPTED SOLUTIONS
v-qiuyu-msft
Community Support
Community Support

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. 

 

q3.PNGq4.PNG

 

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-qiuyu-msft
Community Support
Community Support

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: 

 

w4.PNG

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

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

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you. This works now

v-qiuyu-msft
Community Support
Community Support

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: 

 

w4.PNG

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Community Support
Community Support

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. 

 

q3.PNGq4.PNG

 

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.