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.

tebones

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

Submitted by Advocate II on ‎11-08-2018 01:02 AM

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
See more ideas labeled with:
35 Comments (35 New)
Comments
Michael7
Frequent Visitor
‎02-07-2019 06:20 AM

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
Helper III
Helper III
‎02-07-2019 06:29 AM

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

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

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
Helper III
Helper III
‎02-25-2019 02:53 AM

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
Helper III
Helper III
‎02-25-2019 03:36 AM

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

PaulKn
Advocate II
Advocate II
‎02-25-2019 09:01 AM

@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
Advocate II
Advocate II
‎04-01-2019 08:23 AM

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

Michael7
Frequent Visitor
‎04-01-2019 10:41 AM

Hi,

 

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

 

Thanks,

Michael

PaulKn
Advocate II
Advocate II
‎04-01-2019 11:57 PM

@Michael7 Good news, thank you for the update!

Idea Statuses