Forum Discussion

NaCl-y's avatar
NaCl-y
Frequent Visitor
8 years ago
Solved

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 for...
  • NaCl-y's avatar
    NaCl-y
    8 years ago

    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.

     

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

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.

NaCl-y's avatar
NaCl-y
Frequent Visitor
8 years ago

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

  • Interkoubess's avatar
    Interkoubess
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Hi NaCl-y,

     

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

     

    Hope it helps. 

    • NaCl-y's avatar
      NaCl-y
      Frequent Visitor
      8 years ago

      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's avatar
        NaCl-y
        Frequent Visitor
        8 years ago

        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.