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!

Reply
v-lpathak
Employee
Employee

Power BI export to excel

Hello there,

On Power BI service published report (https://msit.powerbi.com/). Whenever I am exporting my table visual's data to Excel. The date in the excel shows 1900 as a year for all dates.
Sharing the screen shot. Please let me know if you know the solution.

 

vlpathak_0-1632346641520.png

 

Thanks

6 REPLIES 6
v-chenwuz-msft
Community Support
Community Support

Hi @v-lpathak 

 

I'm guessing that the first column of your data is the year and month, and the second column is the day.

normal numeric values changing his type to date would show up in excel as the date after adding that value backwards from 1/1/1900.

My guess is that your column’s value is 12 and 4. If you want to get the date right, you can add them together and change the summed value to a date type.

Like the screenshot below:

vchenwuzmsft_0-1632706371083.png

 

If I am wrong, please send me some screenshots of your table visual and cross out the sensitive data.

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Bryan,

 

You have to educate the user for the option they need to choose for export data. 

 

There are Export data options available, depending on your need you need to choose one of them. 

 

vlpathak_0-1632948844980.png

Thanks

Hi Bryan,

 

For all the visuals in PBI we need to choose “Summarized data” option instead of “Data with current layout”.

The summarized data will give you the formatted output with all the columns as is. However the data with current layout will export the underlined data and especially with the table visuals with date it mess up.

 

You can turn on/off these options with the Report setting in your pbix report file.

 

Screen shot for the option to choose -

vlpathak_0-1632947525919.png

 

Thanks

 

@v-lpathak 

 

Where is the option to keep "Summarized data" enabled but disable "Data with current layout"? None I've tried work.

 

Currently I only see these options:

datawizardry_0-1635432536123.png

 

The last one disables it by greying out the export option entirely. Otherwise it always shows up as an option.


The other problem with the "data with current layout" export option that I noted in another thread is it doesn't keep the same layout order as the visual. It seems to be exporting the column order in the order that they are loaded to the visual. If you rearrange them after the fact, it won't export that way. 

Anonymous
Not applicable

Hi,

Where can i turn on or off other options. Can you please help me. 

I'm having the same issue.  I have a Table Visual with dates such as 8/13/2021 and when I click on Export Data, in excel it shows as 1/8/1900.

Thanks,

Bryan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors