StephanB12

Error when loading data in Datamart

Submitted by Advocate I on ‎05-27-2022 01:16 AM


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

Accepted v-yingjl (Community Support) responded

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

See more ideas labeled with:
35 Comments (35 New)
Comments
JP8991
Helper IV
Helper IV
‎06-28-2022 03:27 PM

@onkelphill still no good for me.

Very frustrating to be honest.

hwmccormack
Regular Visitor
‎07-24-2022 10:08 PM

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
Helper IV
Helper IV
‎07-24-2022 10:47 PM

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

 

Thank you so much for sharing this.

Carlos_Mx_04
Regular Visitor
‎07-29-2022 03:57 PM

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
Advocate I
‎08-01-2022 12:37 AM

@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.

Idea Statuses