Forum Discussion
Invalid date error: Resulting value falls outside the range of allowed values
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),"/"))
Community ChampionHi 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?
