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

[sagivh]

Hi Trond, 

Can you confirm that your locale on the browser and on the desktop? on the desktop its under options->regional settings.

Locale on the desktop is used to determine how to interpret numbers and dates. 

[tebones]

Hi, we are using "Norwegian" locale on our O365 and Power BI tennant. In PBI Desktop my regional setting is set to "Use program language". (model language)

BR
Trond E

[tebones]

Same error with "English" language. My guess is that the data is not converted on the way from the SQL server (with Norwegian settings) to the CDS. When accessing them from PBI the data has errors.

BR
Trond E

[v-qiuyu-msft]

Hi @tebones,

Based on my test, the date format in navigator window is based on OS date format settings. Please change your OS regional settings as Norwegian Bokmål (Norway), then restart Power BI desktop, get data from this dataflow again. 

Best Regards,
Qiuyun Yu

[sagivh]

Hi tebones, 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. 

[tebones]

HI, thanks for suggestions! But after a lot of testing back and forth I can't get this right, and my temporary conclusion is that this is not a client issue. 

I have used this source in several Power BI reports, and have never had this issue before. It happends only when I load data into CDS via dataflow. My guess is that the date value is actually stored as dd.mm in the database and then misinterpreted when I connect to the datetime field fom PBID?

Is it possible to access Dataflow entities via SSMS or some similar tool, whould like to see what is actually sotred in the table?

BR

Trond E

[Anonymous]

@tebones

I have the same issue from Italy. Did you have also problems with decimal numbers with comma as separator? es 100,24 readed al 10024?

I don't think this is a PowerBI Desktop issues but I think it is dataflow eingine itself that on the updating phase do not care about regional settings using USA ones.

[sagivh]

Hi Alessandro, thank you for reporting this, you and tebones helped us narrow down on the locale issue and you are both correct - locale do not seem to persist and we are investigating the root cause and working on a fix. 

I will update this post once I know more about the resolution. 

[tebones]

Good news! And nice to get confirmation on this issue 🙂 Was starting to doubt my BI skills here for a moment 😉

@Anonymous No, I have not seen this also for numbers. But now that we have confirmation, I will wait for a resolution 🙂

BR
Trond Erik

[Anonymous]

Same issue in New Zealand. All settings in PBI Desktop and online, computer, browser ... all set up the same way but dates are not imported correctly in PowerBI desktop. Power Query online shows them in the correct format but errors appear when using the dataflow in PBI Desktop. All dates up to the 12th of a month are ok, after that they aren't. It looks like reversing the format - from dd/mm/yyyy in Power Query online and when importing into Desktop it looks at it in the mm/dd/yyyy fashion, thus rendering anything after the 12th day of a month as an error.