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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MojoGene
Post Patron
Post Patron

Stuck trying to remove default date

My fact table, which comes from a SQL database, contains information about customer bills and payments, e.g., DateBilled, AmountBiled, etc. There is a column for the date when payment was received on the bill, [DateApplied]. For reasons I do not appreciate, the designer of the SQL database populated the [DateApplied] field with a default date of "1/1/1900." This default date appears in the [DateApplied] field in all situations where no payment has been made. Once a payment is made and the date of the payment is recorded, the correct date of payment then appears.

 

My intent is to remove the default date of 1/1/1900 and leave the field blank when no payment has been made. I tried adding this column:

 

NewDateApplied = IF([DateApplied]=1/1/1900,BLANK(),[DateApplied])

I get no error message for this new column, but the default date of 1/1/1900 presistently remains unchanged.

 

Any suggestions would be appreciated.

1 ACCEPTED SOLUTION

What I am trying to say is that I find it strange that khorseman's suggestion isn't working for you, since it does work for me.

Could be your Power BI desktop version, or may be something with the column type.

 

Here is what I did.

 

  1. Create a sample table with two columns. A text and a date column.
  2. Fill the table witj some dummy data, including one date with 1-1-1900
  3. Use Khorseman's suggestion of the value replacer, I can insertt null without issue (see screenshot 1)
    datereplacer.png
  4. Result after the action can be seen in screenshot 2
    datereplacer2.png
    = Table.ReplaceValue(#"Changed Type",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"Column2"})
  5. Since that isn't working for you, you might want to try this work around. (step 6 and beyond)
  6. Replace 1-1-1900 with 1-1-1901
    datereplacer3.png
    = Table.ReplaceValue(#"Changed Type",#date(1900, 1, 1),#date(1901, 1, 1),Replacer.ReplaceValue,{"Column2"})
  7. See what code is generated.
  8. Try to swap that code, with the code that is genarated for a null value

 

Good luck

 

p.s. an alternative would be to edit the SQL statement according to your needs with a CASE statement, you could also try something like

CASE date='1-1-1900' THAN null ESLE date END AS date

Could be a little different based on your datasource, but you can google the specifics of a case statement for your database.

 

 

View solution in original post

6 REPLIES 6
KHorseman
Community Champion
Community Champion

Would it not be easier to do this in the query editor? There is a Replace Values button there. That way you don't need to create a whole second column. Select the DateApplied column and hit Replace Values. Enter 1/1/1900 in the Value To Find box and null in the Replace With box.





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

Proud to be a Super User!




Thanks for the reply.

 

I gave that a try, but the "Replace Value" dialog did not permit me to enter anything other than a Date/Time in the replace box. It would not accept NULL or BLANK.

I tried this in a simple table with a date field. Entered a few dates and did the replce.

It worked just fine.

 

This is the code that was generated by the replacer:

= Table.ReplaceValue(#"Changed Type",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"Kolom 2"})

Salvolin:

 

Thanks for the reply. I am not sure exactly what you are suggesting. Is there another way to replace the Date value other than in the Query Editor? 

What I am trying to say is that I find it strange that khorseman's suggestion isn't working for you, since it does work for me.

Could be your Power BI desktop version, or may be something with the column type.

 

Here is what I did.

 

  1. Create a sample table with two columns. A text and a date column.
  2. Fill the table witj some dummy data, including one date with 1-1-1900
  3. Use Khorseman's suggestion of the value replacer, I can insertt null without issue (see screenshot 1)
    datereplacer.png
  4. Result after the action can be seen in screenshot 2
    datereplacer2.png
    = Table.ReplaceValue(#"Changed Type",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"Column2"})
  5. Since that isn't working for you, you might want to try this work around. (step 6 and beyond)
  6. Replace 1-1-1900 with 1-1-1901
    datereplacer3.png
    = Table.ReplaceValue(#"Changed Type",#date(1900, 1, 1),#date(1901, 1, 1),Replacer.ReplaceValue,{"Column2"})
  7. See what code is generated.
  8. Try to swap that code, with the code that is genarated for a null value

 

Good luck

 

p.s. an alternative would be to edit the SQL statement according to your needs with a CASE statement, you could also try something like

CASE date='1-1-1900' THAN null ESLE date END AS date

Could be a little different based on your datasource, but you can google the specifics of a case statement for your database.

 

 

Salvolin:

 

For some reason, replacing the "1/1/1900" value with "1/1/1901" allowed me to thereafter change the "1/1/1901" to NULL. Go figure. In any event, problem solved.

 

Thanks for all the attention you paid to this. Above and beyond the call of duty.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors