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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Dataflows - UK/US Date Format

Just trying out the Dataflow feature and sadly anything other than US date format does not appear to work.  I am using a DimDate table which has dates in UK format.  Created a Dataflow, all date field that were in UK format came in as US Format - as far as I can see there is no where to set the date format to UK.  So i then connected to the Dataflow from the Desktop and all the date columns error because the service thinks they are invalid dates.  There is life outside of the US 🙂  If this is a bug please fix it quicker than you did when PBI first came out and the same US/UK format issue took nearly a year to fix.

 

DataflowUKFormatError.png

Status: New
Comments
Veles
Advocate V

I've just had a look and the scheduled refresh is still failing with the error message:

 

Error: DataFormat.Error: We couldn't parse the input provided as a Date value. Request ID: b801fa8f-0361-498d-8c1c-97b23cd858db Activity ID: 56368815-053f-4271-b3d4-2e2083b18e0b

 

Query works fine when using Power BI Desktop

sagivh
Employee

Thanks Veles, can you also open the dataflow in the editor and validate its locale and save? Then refresh again.

 

My assumption is that the dataflow locale is en-us. 

Veles
Advocate V

Hi,

 

The locale was English UK but I think I just had to open up the options and re-save the dataflow.

 

I have got an error again but this time it's a #N/A somewhere in my data so looks like the date issue is now resolved.

 

Thanks

sagivh
Employee

Thanks for confirming. 

RGI
Helper III
Helper III

This issue has not been resolved for me (UK).

Is the fix still on the way?

tanyad
Advocate III

@RGI @sagivh - checked yesterday and still not fixed for us (UK).  In the Dataflow changed Options to use UK - dataflows still gives me warnings due to not being able to import all records due to invalid dates.

PaulKn
Advocate II

@sagivh Another UK user here also experiencing this issue with dataflows. When connecting to an on-premise SQL database through a gateway the date appears fine in the Power BI query editor and the entity appears to refresh manually ok. However, when viewed in Power BI Desktop the dates are either transposed dd/mm/yyyy to mm/dd/yyyy or return an error (where the day is greater than 12).

 

The locale of the dataflow is English (United Kingdom) as are the Regional Settings in PBI Desktop. PBI Desktop is running on Windows 7 where the Region is also set to English (United Kingdom). 

 

 

PaulKn
Advocate II

Further to my previous post, I have found that the issue only occurs when using a gateway. Creating a dataflow from the same table/colmns in the the same Azure SQL database without a gateway works fine, but when using a gateway the days and months get swopped. All regional settings on the gateway machine are for the UK. Dates are returned correctly in UK format when using the gateway for a standard Power BI dataset (i.e. the only issue only occurs with dataflows). 

 

I have found that if I change the locale from English (United Kingdom) to English (United States) in the dataflow, save and refresh it then the dates *do* appear correctly in UK format in PBI Desktop...

StephenF
Responsive Resident

Why was this issue not fixed yet?

Agucho
Frequent Visitor

Hi there, any news? I'm having the same issue.

When connecting directly from PBI Desktop to the SQL database, with everything set in English UK format, dates work just fine.

I am now shifting all my dataset's common tables to a dataflow. And although the dataflow locale is also set in English UK, it does not recognize UK format dates.

Any suggestions?
Thanks!