Dataflow Refresh Error: DateTime operation failed because the resulting value falls outside the rang

[hmbedford]

Hi

I have a dataflow setup which pulls from an onprem SQL server (Via gateway). The table has a Year, Month and Day column that I am using to build a date string:

Table.AddColumn(#"Changed column type", "Date", each #datetime([Year], [Month], [Day], 0, 0, 0)),

Table.TransformColumnTypes(#"Added custom", {{"Date", type datetime}}, "en-GB")

I am using this column in the Incremental Refresh Settings, but results in a refresh error that states:

Error: PipelineException: The DateTime operation failed because the resulting value falls outside the range of allowed values. .. RootActivityId = e97cd39f-2f6b-415b-b298-788aba85cab2.Param1 = PipelineException: The DateTime operation failed because the resulting value falls outside the range of allowed values. Request ID: be919842-9f3f-bbd6-69eb-596101612cf9.

I have the project options set Local 'English (United Kingdom)' and converted the DateTime field to en-GB.

Hopefully someone has the anwser 🙂

Thanks

Hayden

[lbendlin]

Some questions: why are you specifying datetime but only feed it a date value ? Is that for incremental refresh?

And what's the reason for specifying the en-GB locale?  That shouldn't really be necessary?

Note that you are not creating a string, you are creating a datetime value.