Forum Discussion
Invalid date error: Resulting value falls outside the range of allowed values
Hello all,
I have an error relating to dates, which arises from an error in data provided by the user. The user had input 29 February for years which are not leap years. Is there a way to fix this to return the same month and year but for 28th (e.g. 29-Feb-22 changed to 28-Feb-22)?
I have tried doing #date(Date.Year(x), Date.Month(x), 28), but it seems like Power Query doesn't even recognise it as a date in the first place.
Thanks in advance.
On the basis of sample provided by you - Use this formula in a custom column
= try Date.From([Date]) otherwise Date.From(Text.Combine(List.ReplaceValue(Text.Split([Date],"/"),"29","28",Replacer.ReplaceValue),"/"))
5 Replies
- Vijay_A_Verma
Most Valuable Professional
Can you post your actual data showing how does it look like in your actual dataset?
As the data is confidential, I would prefer not to. It is basically a table with different columns, one of which is a date column containing different dates.
Col1 Col2 Date Col4 a d 1 August 2025 x b e 29/2/2022 y c f 1 January 2030 z ... ... ... ... Some of the dates are incorrect and invalid, showing a leap day in a non-leap year, e.g. 29 February 2022. I want to make these dates 28 February 20xx (where xx is the relevant year, and in this example it is 2022), while keeping the dates for the other records the same.
- Vijay_A_Verma
On the basis of sample provided by you - Use this formula in a custom column
= try Date.From([Date]) otherwise Date.From(Text.Combine(List.ReplaceValue(Text.Split([Date],"/"),"29","28",Replacer.ReplaceValue),"/"))
- mussaenda
Community Champion
Hi User068765 ,
You can use the function
Date.IsLeapYear
you can do a condition like,
if date is equal to 29 feb and leap year is false then 28 feb else retain the value.
Do correct me if I'm wrong, but I would think that Power Query would need to recognise it as a date field first for this formula to be used. However, Power Query doesn't seem to even recognise this as a date.
For example, if the imported data has the record with a 29/2/2022 field, it will recognise it as "any" data type. To use Date.IsLeapYear, that column has to be recognised as "date" or even "date/time" data type.
I have tried this by using a custom column and it is giving me an error for the relevant records.
Do you have any other ideas, perhaps?
