cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Date Format

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Date Format

This code should work below.

 

=[#"Day"] & "/" & [#"Month"] & "/" & [#"Year"]

Then once you have that column created, convert the data type to Date





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

12 REPLIES 12
Highlighted
Super User IV
Super User IV

Re: Date Format

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Highlighted
Helper III
Helper III

Re: Date Format

Thanks. That was I did. It returned the error.

Highlighted
Super User IV
Super User IV

Re: Date Format

Hi @ask

 

Which one returned the error, Using Locale or re-creating the Date column?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Highlighted
Helper III
Helper III

Re: Date Format

yes

Highlighted
Super User IV
Super User IV

Re: Date Format

I would then suggest trying the second method I mentioned.

 

I have used that in the past to overcome Date issues successfully.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Highlighted
Helper III
Helper III

Re: Date Format

Using th local one, it returns the error

Highlighted
Helper III
Helper III

Re: Date Format

what happen after refreshing the data for the second one, there will be new data coming

Highlighted
Super User IV
Super User IV

Re: Date Format

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Highlighted
Helper III
Helper III

Re: Date Format

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?

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors