Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

tanyad

Dataflows - UK/US Date Format

Submitted by Advocate III on ‎11-08-2018 02:47 PM

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
See more ideas labeled with:
30 Comments (30 New)
Comments
Veles
Advocate V
Advocate V
‎12-21-2018 03:48 AM

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
Microsoft Employee
Microsoft Employee
‎12-21-2018 03:57 AM

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
Advocate V
‎12-21-2018 04:15 AM

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
Microsoft Employee
Microsoft Employee
‎12-21-2018 04:28 AM

Thanks for confirming. 

RGI
Helper III
Helper III
‎02-05-2019 08:09 AM

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

Is the fix still on the way?

tanyad
Advocate III
Advocate III
‎02-05-2019 08:12 AM

@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
Advocate II
‎02-19-2019 08:38 AM

@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
Advocate II
‎02-22-2019 07:12 AM

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
Responsive Resident
‎04-25-2022 03:50 AM

Why was this issue not fixed yet?

Agucho
Frequent Visitor
‎08-23-2022 05:11 AM

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!

Idea Statuses