Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Level up your Power BI skills this month - build one visual each week and tell better stories with data! Get started

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

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

See more ideas labeled with:
35 Comments (35 New)
Comments
onkelphill
Frequent Visitor
‎05-27-2022 01:20 AM

same issue here! 

ACPCI
Regular Visitor
‎05-27-2022 07:28 AM

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

bx5a
New Member
‎05-29-2022 01:39 PM

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?

v-yingjl
Community Support
Community Support
‎05-29-2022 11:06 PM
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

JP8991
Kudo Collector
Kudo Collector
‎05-30-2022 02:26 PM

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.

StephanB12
Advocate I
Advocate I
‎05-31-2022 01:50 AM

Hi,

 

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

 

Best wishes,

 

Stephan

onkelphill
Frequent Visitor
‎05-31-2022 01:56 AM

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

StephanB12
Advocate I
Advocate I
‎05-31-2022 01:58 AM

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,

Stephan

onkelphill
Frequent Visitor
‎05-31-2022 02:24 AM

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

 

onkelphill_0-1653988955538.png

 

Anonymous
Not applicable
‎05-31-2022 07:39 AM

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.

Idea Statuses