cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SandeA
Helper I
Helper I

Converting 01/01/1900 date to a blank field

When a file from a 3rd party was used to create a SQL database, if there was a blank field in one of the date columns, SQL populated the field as 01/01/1900 (my DBA said that is how SQL works). He said he could bring them in as NULLs but I think that would be just as bad.

I'm wanting to create a calculated column where: if the Invoice_Date is 01/01/1900, then put a 0 or blank, otherwise, put the Invoice_date. I've tried using different IF statements with BLANK() and such, but just can't get it right. Is this even possible? 

 

Thank you in advance!

Sande

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@SandeA if you are adding a column using DAX then do the following:

 

New Date = IF ( Table[Date] = DATE ( 1900, 1, 1 ), BLANK(), Table[Date] )

 

if you need PQ solution, let me know.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

Totally agree with @AlexisOlson . Great point. I would have brought null from the SQL and not worry about adding a new column.

 

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





AlexisOlson
Super User
Super User

A NULL in SQL is roughly equivalent to a BLANK() in DAX, so I'm not sure why bringing in NULLs would be "just as bad".

parry2k
Super User
Super User

@SandeA if you are adding a column using DAX then do the following:

 

New Date = IF ( Table[Date] = DATE ( 1900, 1, 1 ), BLANK(), Table[Date] )

 

if you need PQ solution, let me know.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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