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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Error when loading data in Datamart


Hi,

 

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

InternalError
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?

 

thanks,

 

Stephan

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 () :

 

let
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)
in
Quelle1
in
Quelle


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

Comments
JP8991
Advocate II

@onkelphill still no good for me.

Very frustrating to be honest.

hwmccormack
Regular Visitor

Ok I think I found an easy work around. I was having the exact same problem with date columns not working due to the originals being non-US region. You also can't directly modify the table because it is a linked entity.

You can get around this using the method mentioned here: How to modify a linked entity in Power BI dataflows - RADACAD

 

Put Simply:

  1. Create a new table as a Blank Query
  2. enter just one line "= PowerBI.Dataflows()"
  3. Right-click on the Query in the queries pane, and UNSELECT "Enable load"
  4. Right-click on the Query in the queries pane, and select "Reference"
  5. In the new query, Navigate to the Workspace/Dataflow/Table that you want
  6. Repeat steps 4-5 for as many tables as you want.

It's very odd that this works, seeing as it seemingly is not any different to just loading the table from Dataflow natively

 

Untitled.jpg

JP8991
Advocate II

@hwmccormack AMAZING, this works!!!!! Finally, I can actually start testing Datamarts.

 

Thank you so much for sharing this.

Carlos_Mx_04
Regular Visitor

Hello @StephanB12 , the error still persists but alternatively I recommend handling all date formats as a decimal number to avoid the error and change the format in your reports.

 

Hope It helps you.

 

StephanB12
Advocate I

@Carlos_Mx_04 , thanks! I managed to get it working a little while back, but will keep this in mind ifrunning into this issue again.