Stuck trying to remove default date

[MojoGene]

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.

[KHorseman]

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.

[MojoGene]

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.

[Salvolin]

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"})

[MojoGene]

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? 

[Salvolin · 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)
   
4. Result after the action can be seen in screenshot 2
   
   

   = 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
   
   

   = 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.

[MojoGene]

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.