DateTime format error getting data from PBI DataFlow

[Wayfarer]

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 correctly in the online edit entities power query editor.  The format I see in my browser is DD/MM/YYYY, HH:MM:SS AM/PM.  This is good and correct, we are in NZ.  I think in the recent past I have had to make sure my browser - Chrome - is in English NZ language for PBI Service to show dates how they "should" be.

But, when I go to to Power BI Desktop and get data, selecting a table in the dataflow, the preview shows an error for the datetime columns.  An example is:

DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
    16/02/2020 9:26:38 p.m.

I don't see a problem there.  If I go ahead and load the data - I've only done this once, it took a very long time due to the size and amount of data - if I load the data, the error comes through with the load, and I can't seem to resolve it.  "Detect Data Type" or using change data type on one of the columns does not achieve anything.

Why would the Dataflow be fine, but Desktop complain?  Is it regional formatting due to PBI Service and Desktop?  Or is it just "gremlins" of unknown origin?  Is there a solution?

Cheers

[romoguy15]

Hey, @Wayfarer @KNP , I just want to add. This issue for me at least, has resolved it's self months later. When I first started using dataflow, I could not change any of my columns to reflect just the date. I had to leave them under datetime format. Only in power bi desktop I was able to fix this and reformat to just date. Oddly enough now, I can edit the columns in Dataflow to format to date only.

[romoguy15]

Hey @Wayfarer , did you ever figure out a solution to this? I am also having the same issue.

[Wayfarer]

Hey.  I'd forgotten about this post!  I haven't tested the DataFlow region for English (New Zealand) recently, I assume it's still "broken" for datetime columns.  I've just used English (Australia) instead, no difference in the report UI.

Note that regional settings in PBI desktop didn't seem to make any difference.  It was just regional settings in the DataFlow onine.

[KNP]

Hey @Wayfarer, 

I'm curious to hear your results if you have time to revisit this.

I'm not keen to use other similar locale settings as a workaround unless I have no choice.

I'm seeing a lot of weird behaviours for Dataflows in the Service recently, not least of which is the dates.

e.g. I had a generic changed type converting a column to datetime, it worked for almost all rows in the Dataflow but decided to turn some to null. It. Makes. No. Sense. 😕

I then removed the type change and explicitly converted using locale and those nulls formatted correctly as dates. I then removed that locale type conversion and used a generic 'detect data types' and ALL dates converted correctly. No nulls. 

[amitchandak]

Try these setting

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

[Wayfarer]

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

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]

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.