Date values change in excel export

[guidowalter]

Hi,

we are facing an issue when exporting data from PowerBi to Excel - either Online or via PowerBi Embedd. We have a simple table min PowerBi that makes a query to our SQL Server resulting in some columns that are datatype text and two columns that are dataype Date/Time. 

Some of the Date/Time values are 01/01/1900 00:00:000. This is displayed correctly in PBI Desktop, Online and PBI Embedd. 

When we export the data to .xlsx (online and embedd), these values change to 01/02/1900 00:00:000 - so it is not 1st of January and becomes the 2nd of January.  This does NOT happen when we export to .csv! 

I tried to set the locale for the Date/Time column but it doens't make any difference. We know for shure that this was not an issue a few month back, as our customers regulary export these data and if wans't an issue in the first quarter of 2022.

It seems that this is a new bahviour/bug within the xlsx export, but any help is appricated!

Thanks and regards

Guido

[v-xiaoyan-msft]

Hi @guidowalter ,

We have seen two similar icm 310616844 and 301493427 that were reported internally.
Here is the feedback from the PG team.

“Export to Workbook is run on Power BI service machines, which OS may or may not have the same Date Time settings as expected on the client machine's OS.

In order to ensure Export consistency, user should choose a Date format without the asterisk.

As an alternate solution, suggested customer tochange their time format to (hh:nn:ss), and customer is getting the time exported in the same format in Excel after that. So, no issues with hh:nn:ss format (both in data with current lay out and Summarized data options, it works fine). ”

Best Regards,
Community Support Team _ Caitlyn

[guidowalter]

Hi @v-xiaoyan-msft,

thanks for getting back so quick. I tried the suggestions you mentioned and made sure that the date format as no asterix. The error still persists. I don't think that the date format is the culprit here, as the export only changes the date when it is the 01-01-1900 00:00:000 (or any other date format (01/01/1900, etc) for that particular date). In my understanding if the date format or timezone had something to do with it it would change any other date as well, but it doesn't! Also the .csv export works perfectly fine.

As an example, I created a simple report with a fixed table with two value - the example can be found here https://secureshare.swissteach.ch/link/SeBHgNksDqpbY87QvPi7TJ. If i publish it to PBI it shows:

When i export the table to excel it becomes:

No asterix date format, no filters, no transforms, etc. 

Hope this helps to reproduce it and fix it, because i don't know what I did wrong.

Regards

Guido