Dataflow (preview) - dd.mm.yyyy becomes mm.dd.yyyy when consuming data in PBI Desktop

[tebones]

Hi, I am trying dataflows for the first time. Greate feautre by the way! Here is my issue:

When creating an entity in dataflow all looks well:

Note the dateformat is dd.mm.yyyy.

When trying to consume the same data fra PBI Desktop the date is changed to mm.dd.yyyy and the import is not correct.

This is the same data in Power BI Desktop:

Note that SID 02/101 has swapped the dd with mm, and that SID 02/1015 failes because month 18 don't exists.

I guess this is a locale/language issue, but I cant find out where to change it.

Pls advice!

BR
Trond Erik Bones

[Michael7]

Hi,

We are currently unaware of a bug in this area.

Here is what needs to be done:

1. You should set locale in the Authoring tool, save dataflow and refresh.

2. Once you consume Dataflow's data in PBI Desktop, your operating system and PBI Desktop should be set with a desired locale.

If you still experience any issues, you are wellcome to approach us by email - pbidf@microsoft.com

Thanks,

Michael

[RGI]

Hi Michael

I've done all of that. The problem still persists.

The current solution appears to be to format DT columns as text. In my case formatting as "yyyy.mm.dd hh:mm:ss" seems to have done the trick.

This issue is also covered in: https://community.powerbi.com/t5/Issues/Dataflows-UK-US-Date-Format/idc-p/617055

[PaulKn]

@Michael7  I am another UK user who is also experiencing this issue with dataflows when connecting to an on-premise SQL Server database through a gateway e.g. I experience the issue with the "FullDateAlternateKey" column in the DimDate table in the sample AdventureWorksDW2012 database. Everything seems fine in the Power Query editor and it refreshes manually ok, but when I view in PBI Desktop the dates are transposed to mm/dd/yyyy or error when the day is greater than 12.

I have had no problems when building a dataflow using dates from an Azure SQL Database without using a gateway. As an experiment I tried using the gateway to connect to the Azure SQL Database and the issue reoccurred. This would suggest the issue is related to the gateway - however, all the OS regional settings of the gateway machine are set to English (United Kingdom) from what I can see (as well as the locale in the dataflow and PBI Desktop). @RGI Are you using a gateway as well?

[PaulKn]

Further to my previous post, I have found that when using a gateway 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...

[RGI]

I spent about an hour on a screensharing session with MS support, it would appear that they were not aware of the bug. 

Might see a fix in the near future (although it's only due to go out of preview in April).

[RGI]

@PaulKn in answer to your question, yes I am using a gateway.

[PaulKn]

@RGI Thanks - it's good to know you've already been through it with support and made them aware of it. Fingers crossed for a fix soon.

[PaulKn]

This issue is still occuring. @RGI I don't suppose you've had any update from support on this being fixed yet?

[Michael7]

Hi,

We are preparing a fix and current plan is to make it available in two weeks.

Thanks,

Michael

[PaulKn]

@Michael7 Good news, thank you for the update!