Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Hi @Anonymous,
As the issue is resolved now, I will close this thread.
Best Regards,
Qiuyun Yu
Thank you. This works now
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |