cancel
Showing results for 
Search instead for 
Did you mean: 

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

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:

 

image.png


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:

 

image.png

 

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

Status: New
Comments
Frequent Visitor

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

 

1.png2.png3.png

 

Thanks,

Michael

RGI Regular Visitor
Regular Visitor

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

 

Frequent Visitor

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

 

 

 

 

 

 

 

Frequent Visitor

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 Regular Visitor
Regular Visitor

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 Regular Visitor
Regular Visitor

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

Frequent Visitor

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

Frequent Visitor

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

Frequent Visitor

Hi,

 

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

 

Thanks,

Michael

Frequent Visitor

@Michael7 Good news, thank you for the update!