cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
niv_qrm
Frequent Visitor

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

niv_qrm_0-1637670296911.png

 

 

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

niv_qrm_1-1637670344127.png

 

Please see the below. Previously this used to work. 

niv_qrm_2-1637670363603.png

 

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

 

niv_qrm_3-1637670952352.png

 

 

niv_qrm_4-1637670991917.png

 

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

 

Thanks a lot!

 

 

1 ACCEPTED SOLUTION

Hi @v-stephen-msft,

 

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. 

View solution in original post

7 REPLIES 7
v-stephen-msft
Community Support
Community Support

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.

2.png

 

Local try to choose English (United Kingdom)

vstephenmsft_1-1637908007861.png

vstephenmsft_2-1637908027577.png

 

 

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.

 

 

 

 

Hi @v-stephen-msft,

 

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. 

View solution in original post

niv_qrm
Frequent Visitor

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. 

 

niv_qrm_0-1637672112084.png

 

 

niv_qrm_1-1637672141260.png

 

This is the error

niv_qrm_2-1637672174798.png

 

 

Thanks a lot!

 

amitchandak
Super User
Super User

@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)) )

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

niv_qrm_0-1637700830820.png

 

 

niv_qrm_1-1637700892759.png

 

Again, same error 

niv_qrm_2-1637700944216.png

 

Why does it get confused for some dates ?

 

 

Thanks and regards,

 

manikumar34
Solution Sage
Solution Sage

@niv_qrm , 

Click on the error and share screenshot of it, that would help on understanding the exact error





If this helps, Appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




niv_qrm_3-1637672717992.png

This is the error I am getting.

Thank you!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors