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
Wayfarer's avatar
Wayfarer
Advocate I
6 years ago

amitchandak Thanks for the reply; however there is no solution for my problem there.

 

Unless this error is due somehow to regional/locale settings.  If it is, it's not particularly obvious.  For us locale issues tend to present as a date format not standard to NZ, and usually due to one or both of these:

  • Users' local browser settings.  System settings are controlled by policy set by organisation/policy.
  • "Use system" type of date formats (the ones in PBI Desktop with * on them).  Once reports with these in them are published to PBI Service, I tend to see US date formats.  Even though I think a lot of our stuff would go through Australia.

 

This is an issue where datetime fields in PBI DataFlow Entities load/refresh and display correctly.  In PBI Desktop, if use the DataFlow as a data source (Get Data -> Power Platform -> Power BI dataflows), the datetime fields exhibit the error in my first post.

 

Dataflow:

 

Desktop:

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.