cancel
Showing results for 
Search instead for 
Did you mean: 
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
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

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

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

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

 

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors