Forum Discussion

8 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    2 years ago

    In Power Query date between the years 1900 and 9999 are supported. 

    The error says that there is a date 1/1/0099 which is naturally before year 1900, hence not supported. Hence, the operation is failing.

    Either you need to correct this data in source or in Power Query itself.

    • ronrsnfld's avatar
      ronrsnfld
      Super User
      2 years ago

      In Power Query dates are supported from 1/1/0001. The problem lies elsewhere.

       

      let
    Source = #table(1,{{"1/1/0099"},{"1/1/0001"}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}})
in
    #"Changed Type"

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Vijay,

        Can you please explain briefly what is the issue?

        Thanks

  • slorin's avatar
    slorin
    Super User
    2 years ago

    Hi

    Date.From("01/01/0100", "en-EN") = #date(100, 1, 1) 

    Date.From("12/31/0099", "en-EN") = #date(99, 12, 31)

    Date.From("01/01/0001", "en-EN") = #date(1, 1, 1)

    Date.From("01/01/99", "en-EN") =#date(1999,1,1)

    Date.From("12/31/49", "en-EN") =#date(2049,12,31)

    Date.From("01/01/50", "en-EN") =#date(1950,1,1) 

     

    but 

    Number.From(#date(100, 1, 1) ) = -657434

    Number.From(#date(99, 12, 31) ) => error

     

    Stéphane

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Stephane,

      Thanks for the input.

      We have a query, we are not getting the date ranges from 01/01/0001 to 12/31/0099 in the report view.

      Kindly guides us haow to resolve this issue.

       

      Thanks,

      Priyanka 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous 

         

        It seems the date ranges from 01/01/0001 to 12/31/0099 are not supported in Power BI. Below is my test result. 

         

        From the official documentation, only years between 1900 and 9999 are designed to be supported. 

        Data types in Power BI Desktop - Power BI | Microsoft Learn

        DATE function (DAX) - DAX | Microsoft Learn

         

        Although the years between 0100 and 1899 appears supported at present according to the test, this cannot be always guaranteed as they are not in the designed allowed range.

         

        Best Regards,
        Jing
        If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!