Pasre date giving error

Hi,

I am not sure what I am doing wrong, but when I try to Parse date I am getting error after 13th of the month.

I have done this before and worked previously but for some reason I am getting error.

Order date is in this format

If I usually extract the date part and do Parse date.

Please see the below. Previously this used to work.

I tried Change Type with Locale and I am still getting the error.

Please help! I am not sure what else to try and why I am getting this error.

Thanks a lot!

Need Help

niv_qrm
4 years ago
Hi Anonymous,

Thanks for your reply. I figured out the mistake but forgot to come and check the replies here.
This table is a mix of data coming form different systems with different date formats.
So I seperated those ones with different format, then split the date and concatenated it and then used parse date and it fixed. For some reason when I try parse date isn't working when the data date format in US format. I tried using the Locale with UK, UAE but still I kept getting error. This is the only way I could fix it.

Thanks a lot for the replies.

7 Replies

Anonymous
Not applicable
4 years ago
Hi niv_qrm ,

See the sample input values, they're DD/MM/YYYY, but your column from 'from example' is MM/DD/YYYY, so it will be wrong.

Local try to choose English (United Kingdom)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Pasre date giving error.pbix43 KB
- niv_qrm
  Frequent Visitor
  4 years ago
  Hi Anonymous,
  
  Thanks for your reply. I figured out the mistake but forgot to come and check the replies here.
  This table is a mix of data coming form different systems with different date formats.
  So I seperated those ones with different format, then split the date and concatenated it and then used parse date and it fixed. For some reason when I try parse date isn't working when the data date format in US format. I tried using the Locale with UK, UAE but still I kept getting error. This is the only way I could fix it.
  
  Thanks a lot for the replies.
manikumar34
Solution Sage
4 years ago
niv_qrm ,
Click on the error and share screenshot of it, that would help on understanding the exact error
- niv_qrm
  Frequent Visitor
  4 years ago
  This is the error I am getting.
  Thank you!
amitchandak
Super User
4 years ago
niv_qrm , because power assume date will come in your system format only. So it want date in dd/mm/yyyy format and getting date in mm/dd/yyyy format

Change you system setting to US format or treat is as text and get date in another format

MM/DD/YYYY to DD/MM/YYYY
#date( Number.FromText(Text.End(DD__MM__YY[date],4)),Number.FromText(Text.Start(DD__MM__YY[Version_Id],2)), Number.FromText(Text.Middle(DD__MM__YY[date],3,2)) )
- niv_qrm
  Frequent Visitor
  4 years ago
  Hi amitchandak
  I have been changing the format easily without much issues till today.
  I tried keeping it as text and changing the format like you mentioned but I still got error.
  
  What interesting is I have a list of dates that are done in the correct format even for the dates after 12th of the month and then there is a bunch with errors
  
  Again, same error
  
  Why does it get confused for some dates ?
  
  Thanks and regards,
niv_qrm
Frequent Visitor
4 years ago
Just wanted to add, I also created another column " from example" and changed the date to dd-mm-yyyy and tried to parse this column. The first few columns came well, but then other dates are still showing error.

This is the error

Thanks a lot!