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
Paulx99
Helper IV
Helper IV

Exporting data from Power BI to Excel

Hi all

 

Does anybody know (as per the post below) when 'export data to CSV' was actually implemented in PBI Desktop ? I currently have PBI Desktop (Mar 2016) installed, but can't see it in there - I would have expected to see an 'Export' option, with a 'CSV' sub option in the File menu. Possibly, given the date of the response below from Admin (29 Mar 2016), it may be coming in the Apr 2016 update ??

 

The above said, I'm struggling to undersatnd why an export to CSV option has been put ahead of an export to Excel option (and why there were no data export options at all, before the issue was raised !!) No file loads better into Excel than an Excel (.xlsx) file. In addition, there can sometimes be structural issues with CSV files. Given Excel and Power BI are both MS products, I would have thought it would be obvious and straightforward to provide an 'Export to Excel' option in the File menu in PBI Desktop.

 

Paul

 

How can we improve Power BI?

Export PowerBI Desktop to CSV/Excel

Please provide the ability to export PowerBI desktop reports to csv/excel, some users need the data from time to time

3,264 votes
Vote Vote Vote
Vote
Sign in
prestine
Your name
Your email address
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
Password icon
 
I agree to the terms of service
Signed in as (Sign out)
1 vote 2 votes 3 votes Remove votes
You have left! (?) (thinking…)
Anonymous shared this idea  ·  September 04, 2015  ·  Flag idea as inappropriate…Flag idea as inappropriate…  ·  Delete…  ·  Admin →
 
completed  ·  Lance DelanoAdminLance Delano (Admin, Microsoft Power BI) responded  ·  March 29, 2016

We’ve implemented export data to CSV. You can open a CSV file in Excel — Excel is often the default program to open CSV files. We hope this handles this feature request. If it does not, please open a new idea.

33 REPLIES 33

Hi Guys, 

 

I have just seen this thread, but this workaround might help anyone in the future: 

if you want to export to csv from BI Desktop including all your calculated columns. Put a table visual, add all your fields. Press ... and export.

 

Hope this helps.

 

Natalia  

Please refer to my reply to Sean. What I want to be able to do is export data from the Data screen in PBI Desktop, NOT data from the Report screen in PBI Desktop. Exporting data from the Report screen to either CSV or Execl is of no interest to me.

Sean
Community Champion
Community Champion

@Paulx99 I wrote about this on 8/23/15

 

http://community.powerbi.com/t5/Desktop/Moving-Rearranging-Calculated-Columns-in-Data-View-in-PBI/m-...

 

Ctrl-A is to Select All - So I can Copy and Paste in Excel

 

I agree with you we should be able to do this

 

"Also please enable Ctrl+A (Select ALL) in the Data View - currently it works only in the Query Editor.

This way we could Select ALL Data in the Data View where it would include the Calculated Columns."

Hey Sean

 

I'm fully aware that a CTRL-A (Select All) will let me copy and paste from the data screen (albeit in Query Editor) into Excel. That is however, not not going to 'cut the mustrad' for me ! What end's up being pasted into Excel is messy to say the least and requires reformatting. For a tool as sophisticated as PBI Desktop, it's almost embarrassing that this is currently the only way to 'export' data from the Data View in PBI Desktop to Excel.

 

As I said in my original post on this issue last night, I find it incredible that there is no Export option in the File menu in PBI Desktop to export data from the Data View to Excel (.xlsx). It should be a two way street - if we can import Excel data into PBI Desktop, we should be able to export PBI Desktop data back to Excel.

 

Glad you agree with me on this one !

 

Paul

 

 

Sean
Community Champion
Community Champion

Yes I agree 100%!

 

We need a 1-click Export the Data View grid to Excel

 

Would be nice if the calculated columns are included as well but don't know if that would be feasible

(maybe only as values not formulas)

 

EDIT: We have to give credit to the PBI team though they have been working hard improving and adding new features all the time

 

Here's a link to one of my ideas... If anyone likes it please vote

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9207006-matrix-collapse-expand-bu...

Your idea of a '1-click Export the Data View grid to Excel is a good one and that would do the job. I would definately want calculated columns to be a part of any export to Excel.

 

As for the Power BI team, there is no doubt that what has been achieved with this product to date is truly amazing and I agree - full credit to them for that. 

 

That said, the deeper I'm getting into this product (PBI Desktop), the more I'm I'm noticing that from a data analytics point of view it appears to be lacking somewhat. For example, in a couple of DA (data analytics) products I've used, there is an Export command, where you can export any data view (including one that contains both physical and calculated fields) to Excel (xlsx or xls), any type of delimited file, Access, Text, or XML - there are many others as well.

 

If PBI Desktop is just meant to be a BI product, I can sort of see why something like the exporting of a data view in PBI Desktop would not be a top priority. If on the other hand, Power BI Desktop is meant to be both a BI and DA product, that's exactly why I'm scratching my head wondering why I can't export the Data view to Excel (including calculated fields) ! The top destination for DA outputs (exceptions) from DA software is Excel.

 

Most BI products I've seen are very strong on visualisations, but not so strong on data analytics capabilities, while most DA products I've seen are obviously very strong on data analytics capabilities, but not so strong on visualisations. What I've seen so far with PBI Desktop would appear to confirm this, (for me anyway).

 

PS: I will vote for your idea !

kcantor
Community Champion
Community Champion

@Paulx99

First off, I agree, exporting to excel would be an ideal solution for several reports that I have where the PTB want more details. In fact, I would prefer to bypass Excel and export directly into PowerPivot as the engine is much more robust and capable. However, I have heard (can't remember the source) that PowerPivot and PowerBI were supposed to be the souped up version of Excel designed to replace other software programs and the inevitable "Export to Excel" button.  The argument was presented that if all those other software programs were the end all to DA, why did they need to export to Excel? So, (again, can't remember the source) Microsoft created PowerPivot and PowerBI.

Question: If you can build reports and dashboards using DAX in PowerBI, and you build the data model in Query Editor (or Power Query) What would you do with the data you export that you cannot do within the software?

For me, it is a large matrix showing more information than one screen can handle . . . especially for printing purposes.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Not too sure about the exact evolution here, but my guess is that PBI Desktop appears to be bringing together in the one piece of software most of the capabilities of the four Excel add-ins of Power Pivot, Power Query, Power View and Power Map plus other new, additional, functionality. In answer to your question, please refer to my reply to ryans. The answer is basically that I need to export data to Excel because I'm doing descriptive analytics, not predictive analytics. As such, I find not having an 'Export to Excel' option in PBI very limiting.

Sean
Community Champion
Community Champion

@kcantor If you would like to see Collapse/Expand Buttons in the Matrix

 

Please Vote here - https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9207006-matrix-collapse-expand-bu...

 

We have 473 Votes so far!

 

Paul Thanks for voting. Hopefully they'll consider it soon...

kcantor
Community Champion
Community Champion

@Sean

I have added votes.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks Sean.

Export Data.png

 

@Paulx99 look in the top right corner of a visualization (...) - defaults to data.csv - as @Greg_Deckler said you can rename but not change file format

 

EDIT:  By the way after you open the data.csv and make any change (just make a column wider) and then try to save you'll see this

CSV Data File.png

And as soon as you hit NO - it will default to xlsx.

Hi Sean

 

Appreciate your reply, but we might be talking at cross purposes here. I'm not interested in exporting the data from a visualisation in the Report screen to either CSV or Excel. What I want to be able to do is to export data from the Data screen to Excel. That's why I was looking for a File -> Export -> Excel option in the File menu. But there is no such option and there is no CSV option there either.

 

Paul

 

 

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.