HELP - We couldn't parse the input provided as Date Value

[Anonymous]

Hi Guys, 

I am trying to convert period date column from text to a date format, but somehow it returns an error. I've tried looking for answers in the forum (Split dates and merge/changing locale) , however still getting an error for this. Is there any workaround for this?

Regards

[FarhanAhmed · Accepted solution]

Kindly check if this resolves your problem.

1. Edit the table 

2. Split the column with '/' custom delimiter

3. Re-arrange the columns to see the month in first and date column to next and year to the last.

4. Select all the three columns.

5. Right click-> Merge columns with a custom delimiter '/'.

6. Transform the column to change the data type to 'Date'

Ref: Solved: How to convert dd/mm/yyyy to mm/dd/yyy or any date... - Microsoft Power BI Community

Ref: Solved: Date Change from DD-MM-YYYY - Microsoft Power BI Community

[Anonymous]

This reply solved my question. 

Steps to solve:

1. Split initial date format into 3 columns date ,month and year (using extract function)

2. Combine/merge in order of year/month/date

3. Change format from text to date

4. Error does not appear

[Anonymous]

Hi Farhan,

yes i've tried this. refer to the merged column in my first post. This was created based on splitting the characters by delimiters and then merging them . After converting to date, an error appears. 

[FarhanAhmed]

For Merging columns you need to view order of the column that you are merging.

check for similar statement below in your power query code. and make sure that the columns that are combining are in order of YYYY-MM-DD

 #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"DT.3", "DT.2", "DT.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged")

[Jihwan_Kim]

Hi,

I am not sure if you want to use Month/Day/Year format or Day/Month/Year format.

I assume you want to use Day/Month/Year format.

Please try the below.

In power query editor,

select the column -> right click -> change type -> using locale -> select your version

[Anonymous]

Hi Jihwan ,

I've already tried changing locale, but still showing error. 

Thanks

[Jihwan_Kim]

Can you share your sample pbix file's link (One Drive link or any other link) and let me see the Power Query Editor?

[Anonymous]

Hi  Jihwan,

Refer to this

https://drive.google.com/file/d/1B0vtuUQFMpP97oes3ZOs8srREOaikc-p/view?usp=sharing