Forum Discussion

Wayfarer's avatar
Wayfarer
Advocate I
6 years ago

DateTime format error getting data from PBI DataFlow

Hi all.  Hopefully someone has come across the same issue I'm finding and has some advice.   I have created a Power BI dataflow.  It refreshes successfully.  There are date fields which show correc...
Need Help
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Wayfarer ,

 

Sorry for that we cannot reproduce this issue on my side, What is the format of this column in the data flow? Have you tried to use Culture Code to convert it to datatime format in dataflow, such as the "mi-NZ" in our query?

 

Our query in dataflow:

 

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI00zcw0jcyMDJQsLQyMrMytlAo0MvVU4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Time = _t]),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"ID", Int64.Type}, {"Time", type datetime}}, "mi-NZ")
in
  #"Changed column type"

 

Our query in Desktop:

let
    Source = PowerBI.Dataflows(null),
    #"ID" = Source{[workspaceId="Workspace ID"]}[Data],
    #"WorkspaceID" = #"ID"{[dataflowId="Dataflow ID"]}[Data],
    Table1 = #"Dataflow ID"{[entity="Table"]}[Data]
in
    Table1

 


Best regards,

Wayfarer's avatar
Wayfarer
Advocate I
6 years ago

Hi.

 

Thanks for trying to help.  I may have found the cause.  But before I get to that, I have checked that the columns are all Date/Time type and they are.  They come from an SQL database where the columns are datetime2(7).

 

In the DataFlow Project Options, the locale is set to English (New Zealand).  I have tried changing column type with a locale (right click the column in DataFlow Power Query editor --> Use locale) and picking Date/Time and English (New Zealand).  The error persists in Power BI Desktop.  I also tried changing the column type locale to English (United Kingdom), but the error persists.

 

I have made a second DataFlow, but set the Project Options locale to English (United Kingdom).  This works.  The datetime fields don't have the error in Power BI Desktop.

 

It also seems to work with DataFlow locale English (Australia).

Power BI Desktop options for locale doesn't seem to make a difference to this.

 

So perhaps there is some problem with English (New Zealand), I don't know.  It is very strange.