Dataflows - UK/US Date Format

Status: New
by tanyad Frequent Visitor Thursday

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

Comments
by Moderator v-qiuyu-msft
Thursday

Hi @tanyad,

 

Please change your OS date format as UK, then restart the Power BI dekstop. Reopen the report, refresh query in query editor to see if the issue is gone. See: https://community.powerbi.com/t5/Issues/Dataflow-preview-dd-mm-yyyy-becomes-mm-dd-yyyy-when-consumin...

 

Best Regards,
Qiuyun Yu 

by tanyad Frequent Visitor
Friday

I am in the UK, my OS date format is the UK, Power BI Desktop is set to UK, the Dataflow is set to UK and finally, my browser is UK.

by sagivh Frequent Visitor
Sunday

Hi tanyad, there is another language settings location and that is within the power query editor - can you make sure to open that dataflow, and within the editor click options and set the right locale? then save the dataflow and make sure to refresh. the data will be refreshed with the right locale. 

 

 

by tanyad Frequent Visitor
Monday

As per my previous post everything is in UK incuding the dataflow and still it stays in US format.

by tanyad Frequent Visitor
Monday

@sagivh @v-qiuyu-msft - some additional information.  It seems that when you set the locale in the options of the Dataflow, after a certain amount of time it reverts back to US.  When i first posted this problem I had changed the local in the query editor to that of the UK.  When i received @sagivh comment i went in to double check and it was set to US so i set it back to UK and refreshed - still the same problem.  Now some hours later i have gone in and checked again and the local has reverted itself back to US.  I am taking a screen shot of it set to UK and will check later to see if it is set back to US.  and before you ask YES i am saving the changes :-)

by tanyad Frequent Visitor
Monday

@sagivh @v-qiuyu-msft - just clarify how things stand now (and I have refreshed the data) .... I will check back later to see if it has reverted back to US locale2018-11-12_19-52-53.png

by sagivh Frequent Visitor
Monday

Hi tanyad, thank you for reporting this issue, you are absolutly correct about the fact that locale doesnt seem to persist and we are actively investigating the root cause here and will work on a fix. 

 

I will follow up once I have more information about resolution. 

 

 

by tanyad Frequent Visitor
Monday

@sagivh- Many thanks.  Just did some more testing and you might like to know that there is an issue when resaving - and i think this also causes it to revert back.  Quick steps.  1) Open Entity and set local. 2)Click [Done] 3) Click [Save] Dataflow 4) Re open Entity change something like format a column 5) Click [Done] 6) Click [Save] dataflow - it then errors with this    2018-11-12_21-03-40.png

 

Idea Statuses