Error in the Date format

[PriyankaNag]

Hi Team,

Could you please help me in fixing the below error face in Power BI:

Expression.Error: The Date operation failed because the resulting value falls outside the range of allowed values.
Details:
    1/3/0001

Any suggestion or advice will be highly appreciated.

Thanks

[slorin]

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

[PriyankaNag]

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 

[ronrsnfld]

If all you need to do is have them for a Power BI report, and you don't need to do any date calculations in Power BI, you could convert them to text strings.

eg:

#"Text Dates" = Table.TransformColumns(#"Previous Step",{"Date Column", each Date.ToText(_,"MM/dd/yyyy")})

In the Advanced Editor of PQ, change "Previous Step" and "Date Column" to the appropriate step and column name as required. And the format to whatever it is you prefer.

Above in Power BI

[Anonymous]

Hi @PriyankaNag 

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!

[Vijay_A_Verma]

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]

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"

[PriyankaNag]

Hi Vijay,

Can you please explain briefly what is the issue?

Thanks

[ronrsnfld]

Not without seeing your data and code. And I'm not going to copy a screenshot.