Dataflows - UK/US Date Format

[tanyad]

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.

[Veles]

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]

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]

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]

Thanks for confirming. 

[RGI]

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

Is the fix still on the way?

[tanyad]

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

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

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]

Why was this issue not fixed yet?

[Agucho]

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!