Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
NaCl-y
Frequent Visitor

0000/00/00 Date format error

Hello all

 

I have a problem while in Query Editor. On the left screeshot , you'll see that TEDSD is in the good date format without any error. I try to change the text form of LPRD into a date format, errors are popping out. It does not recongnize 0000/00/00 as a date format. 

 

Can someone help me find a solution? It is important that it is in Date format.

 

Thanks in advance! 

Date Rip.pngRip date 2.png

2 ACCEPTED SOLUTIONS
NaCl-y
Frequent Visitor

I found how to fix the error. 

 

First convert the Text form column into a Date Format. You'll see the error pop up. Now in Query Editor, go in the "Transform" ribbon and look for "Replace Value", click on the small down arrow besides it and choose "Replace Error". A small windown will pop out and you can input the value to replace the error. The value that will replace the error has to be the same Type as the values in that column.

 

Fix Error.png

View solution in original post

Hi @NaCl-y,

 

You also can replace it in the first place. Please refer to the following snapshot.

00000000_Date_format_error

 

 

Best Regards,

Dale

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

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

You could try doing a replace of 0000/00/00 to null or perhaps convert it to something like December 30th, 1899, which is the base date for DAX before you do the date format conversion.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I'm still pretty new to Power BI, how do i change it to NULL?

Hi @NaCl-y,

 

You can use in DAX the IF function combined with FORMAT to have your expected date.

 

Hope it helps. 

I tried creating new columns using this code

 

LRPD_REAL = IF(SAP_Transaction_ZP26[LPRD] = "0000/00/00", BLANK(), SAP_Transaction_ZP26[LPRD])

and then try to convert the text of the new column into a Date, however the calculated column does not appear in the Query Editor.

 

NaCl-y
Frequent Visitor

I found how to fix the error. 

 

First convert the Text form column into a Date Format. You'll see the error pop up. Now in Query Editor, go in the "Transform" ribbon and look for "Replace Value", click on the small down arrow besides it and choose "Replace Error". A small windown will pop out and you can input the value to replace the error. The value that will replace the error has to be the same Type as the values in that column.

 

Fix Error.png

Hi @NaCl-y,

 

You also can replace it in the first place. Please refer to the following snapshot.

00000000_Date_format_error

 

 

Best Regards,

Dale

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors