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,
date format in the original data source is text format as shown below .
30/05/2017 |
I want to convert it into US date format as date type. It returns error : (even though I use the change the data type (local)
DataFormat.Error: We couldn't parse the input provided as a Date value.
Details:
30/05/2017
Solved! Go to Solution.
This code should work below.
=[#"Day"] & "/" & [#"Month"] & "/" & [#"Year"]
Then once you have that column created, convert the data type to Date
Hi @ask
If you go into the Query Editor, and click on the drop down for your Date column there is an option to say "Using Locale"
If for some reason your PC is set to a different Locale, the other option I can select is to split your column by the "/" delimiter, which will then create 3 columns for Day, Month & Year and then recreate the column by using the Columns from Example in the format you need. And then you can change the data type to Date.
Thanks. That was I did. It returned the error.
yes
Using th local one, it returns the error
what happen after refreshing the data for the second one, there will be new data coming
Hi @ask
It will update as new data comes in because it is based on your original column. So no data or dates will be lost.
I create a year column by using Add Column - Custum Column
Year = right([Date],4)
Expression.Error: The name 'right' wasn't recognized. Make sure it's spelled correctly.
Any direction?
i use the split column function to get the Year, month and day.
i try to create a new date by using
date= datevalue(CONCATENATE((CONCATENATE([Month],[Day])),[Year])) from create Add Custom Column.
It returned error:
Expression.Error: The name 'datevalue' wasn't recognized. Make sure it's spelled correctly.
This code should work below.
=[#"Day"] & "/" & [#"Month"] & "/" & [#"Year"]
Then once you have that column created, convert the data type to Date
Hi @ask
This is because "right" is an Excel formula
You can try the following:
Year = Date.Year([Date])
Your Date column must have the data type of Date
I would then suggest trying the second method I mentioned.
I have used that in the past to overcome Date issues successfully.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |