Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Error when loading data in Datamart



I am trying to add dataflows to a datamart, however when loading up the data to the datamart, I am getting an error that my date column is not a valid date/time field. Please see error below.


Wrapping things up

ErrorMessageString was not recognized as a valid DateTime.
HttpStatusCode 500


I am suspecting it is because the locale seems to be resetting itself (to United States) after you load data in the dataflow.


Could you please have a look?





Status: Accepted

Thanks for @Anonymous '' sharing, users who have faced with the same issue could try his workaround:


It assumes a valid table and a string column list as parameters and assumes the columns are of type text.


Feel free to use and adapt it (e.g.making it more generic for different TZs).


Function TransformSQLDTZ () :


Quelle = (Tab as table, cols as list) => let
Quelle1 = Table.ReplaceValue(Table.ReplaceValue(Table.ReplaceValue(Tab,"+01:00","",Replacer.ReplaceText,cols),"T"," ",Replacer.ReplaceText,cols),"+02:00","",Replacer.ReplaceText,cols)

Implementation  (query code snippet, copy to query code, adapt "StepBeforeDateConversion" to your previous step name) :

ChangeDatestoText = Table.TransformColumnTypes(StepBeforeDateConversion,{{"AnalyticsUpdatedDate", type text}, {"CompletedDate", type text}, {"StartedDate", type text}}),
TransformSQLDTZ = TransformSQLDTZ(ChangeDatestoText, {"AnalyticsUpdatedDate","CompletedDate", "StartedDate"}),

After those amendments you should be able to import datetimes/datetimezones successfully into datamart (as text values).



Best Regards,
Community Support Team _ Yingjie Li

Frequent Visitor

same issue here! 

Regular Visitor

Same issue, in my case it has paralyzed all my dataflows that are stored in azure, and I get the bugs described in the photosDiapositiva1.JPGDiapositiva2.JPG

New Member

Same thing. In my case the culprit is the extended date table from EDNA which we have as a dataflow.


Simply can't ingest it into the datamart.


Tried recreating it as a new blank query in the datamart itself. Doesn't work either.


Anyone figured out a workaround?

Community Support
Status changed to: Investigating

Although could not reproduce this issue, have other problems when testing the datamart as far as my test.


When I connected to an excel file in a dataflow, it has a date column and a value column with 3 rows. After creating a datamart and connecting to this dataflow, it would not remind the error but no rows show in the table.


Will continue to investigate the issues about datamart, if you have more detail about this issue, like the data source for the dataflow, the workspace type etc. which would help better to collect related information, you can share here.


Best Regards,
Community Support Team _ Yingjie Li

Advocate II

Same issue for me too.

Hope this gets sorted too as I cannot use the feature currently as we rely heavily on Dataflows in our Business.

Advocate I



I managed to load in my data this morning, so issue seems to be resolved.


Best wishes,



Frequent Visitor

@StephanB12  i cannot confirm. Just tried again to load some dataflow and i did not succeed! 

Advocate I

Hi @onkelphill,

Please note my issue was related to loading up data in the new feature data mart, not a dataflow.

Not sure if this makes a difference?

Best wishes,


Frequent Visitor

@StephanB12 thanks.


Just to add some detail, this is the error message. The dataflow holds just 1 table. There is 1 column of data type date, correctly formatted. 




Not applicable

I have the exact same issue and same error message.

In my dataflow the datetime columns are being created via the datetime function of power M. So it should really be valid datetime. I also tried to add a regional scheme to the column in the data flow, but the 500 error persists.