Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Dates being read wrongly in PBI Service

Hello everyone,

 

Power BI Service seems to behaving differently with dates.

 

In the desktop file I have changed the setting to not detect data types.

When I referesh the file in desktop, everything works fine, all the dates and the corresponding numbers are showing up correctly.

However, its a different story in the service. When the refresh takes place from the service, it converts the date format, which originally is dd/mm/yy to mm/dd/yy and due to which 1st of September becomes 9th of January and so the measures go haywire.

 

I have raised a ticket with Microsoft regarding this issue and it has been 2 months already and they have not been able to solve the problem. Here is the ticket number if anyone from Microsoft is reading this 120071524005859.

 

Time Intelligence is a HUGE part of Power BI and Business Intelligence in general and a product like Microsoft Power BI cannot mess it up.

My only question is that if it is working in the Power BI Desktop, it should work the same way in Power BI Service.

 

More than an idea, it is a request to fix the bug in the product/service.

I hope Microsoft resolves this problem soon before anyone else loses clients, like I did.

 

Thank you,

 

Vishesh Jain

Status: New
Comments
v-chuncz-msft
Community Support

@visheshjain 

 

Since you've raised a ticket, just stay tuned. By the way, you may try the optional parameter culture of Table.TransformColumnTypes.

visheshjain
Solution Supplier

Hello @v-chuncz-msft ,

 

I have tried it already and have informed the support team as well, but it has not worked.

 

I had started a discussion in the community as well, so if you like please read this thread which explains my problem in detail.

 

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?GroupId=547&MessageK...

 

I hope MS resolves the issue soon.

 

Thank you,

jacknoble
Frequent Visitor

This is a major bug that keeps causing problems for my organisation. I've had to four different people report this issue to me, and so far I haven't been able to discover any cause. 

 

The other thread on PBIUG looks promising; it might be a good idea to rename this issue report to something that summarises the issue better and helps other users find it.

visheshjain
Solution Supplier

Hi @jacknoble ,

 

I have opened a ticket regarding this with MS and its been 9 months that they are still investigating the issue.

 

The product team is so adamant that they won't even talk to me.

One of the senior advisor on this ticket also acknowledged the bug/problem regarding the dates and agreed that the product team should get the fundamentals right, before rolling out new features.

However the product team just wouldn't budge on this.

 

They are still working on the issue and I'll update this once the issue is resolved.

Meanwhile, if you have any suggestions regarding the renaming of the ticket the please let me know.

 

Thank you,

 

Vishesh Jain

visheshjain
Solution Supplier

Hello everyone,

 

So whosoever is still interested in the solution to this, after over a year the problem has been located.

I would not say that it is solved, but there is a minor workaround.

 

So the files I was using were .xls format i.e before 2007 format and that was causing the problem.

Due to some ACE driver that .xls format uses, Power BI was reading the dates wrong in the service.

So converting the files into .xlsx format i.e 2007 onwards MS stopped using the ACE driver and PBI was fetching the correct dates.

 

One other problem I noticed with the xlsx files is that, even after setting PBI to NOT detect data types, PBI was still converting data types.

 

However, try using the xlsx format, instead of the xls format for your excel files and I hope the date issue will be solved.

 

Thank you,

 

Vishesh Jain