Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Wayfarer
Helper I
Helper I

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

8 REPLIES 8
romoguy15
Helper IV
Helper IV

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
Helper IV
Helper IV

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

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.

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. 

 

 

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
amitchandak
Super User
Super User

Try these setting

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

PBI DataFlow Power Query DateTime.PNG

 

Desktop:

PBI Desktop Power Query DateTime format error.PNG

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

 

5.jpg6.jpg7.jpg


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.