Forum Discussion

MojoGene's avatar
MojoGene
Post Patron
10 years ago
Solved

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.

Need Help
  • Salvolin's avatar
    Salvolin
    10 years ago

    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.

     

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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's avatar
      MojoGene
      Post Patron
      10 years ago

      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's avatar
        Salvolin
        Frequent Visitor
        10 years ago

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