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
hughswells
New Member

Woohoo! Export to XLSX just appeared. Happy New Year!

ryans
Helper I
Helper I

Why do you need to export to excel? My guess is that anything you need to do in excel the query editor or calc columns/measures could do just as well. If it is the case that you are more familiar with Excel you can do the same with the combination of Power Query (Get & Transform in 2016) and Power Pivot. If your looking for even more advanced analytics, design a predictive analytics model in the Cortana Analytics Suite and deploy it as a web service. With Power Query or the Query Editor you can call on this webservice and visualize the outcomes of those predictions.

Firstly, I'm definately not trying to do things in Excel that can be done in PBI - that would make no sense, given I have PBI.

 

The answer to your question as to why I need to export data to Excel is that I'm doing descriptive analytics, not predictive analytics. I look at data retrospectively to isolate very specific items of concern (exceptions), which I then need to export to Excel for end users (who know nothing about PBI and have no need to use it), but who do know how to use Excel, so they can view and/or investigate these exceptions further themselves or, distribute them to others for further investigation and feedback. For this reason, from a DA (data analytics) point of view, not having an 'Export to Excel' option in PBI seems like a major oversight to me.

 

Rather than PBI Desktop just being a data destination and downloadable production environment where results untlimately end up being published in PBI Service for consumption by end users, to me, PBI Desktop should also be looked at as being a powerful analysis AND conversion tool - loading data in a certain format from one system and exporting it to another system in a completely different format. Such capability (including being able to export data to Excel) is standard for any major DA software out there.

 

I think MS needs to decide if PBI is meant to be a BI tool only or if it is intended to be a BI AND DA tool. From what I've seen so far, it is definately a BI tool (and a very good one at that), with some DA capability. In my view, it still has a long way to go to match the DA capabilities of other major DA software providers out there. Being able to export data from the Data View in PBI Desktop to Excel would be a good start for those of us that need to perform descriptive analytics.

 

 

 

 

Sean
Community Champion
Community Champion

Pivot Tables in Excel are in Compact Form and have Collapse/Expand buttons.

 

You can summarize the fields and fit a lot of data on one Page! You can Expand only the fields you want to see the details for

 

With the Matrix in PBI you can't - you have to scroll and scroll and keep scrolling... to get to what you want to see...

 

Slicers remove data AND field Totals in the Matrix so they do not help

 

 

 

 

Agree.

So current state seems to be:

 

If you don't want to drag your data through the cloud but need to perform operations that need a Solver, don't use Power BI but Excel instead.

 

Best current workaround I know of is to export your data to txt using DAX Studio (unlimited number of rows & you can write your own DAX-statements if you want to export measures as well). Then re-import it into Excel via Power Query (you can even copy your formatting-statement from PBI to be applied there as well) & table output that feeds into Solver.

 

You could also use the txt instead to feed an R-solver (this blogpost describes how to execute an R-script within a PBI-query) whose results will be played back into Power BI for display.

 

 

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

While no data that I work with from a DA perspective would ever be dragged through the cloud, items of interest identified within that data in PBI Desktop would definately (for my purposes) need to be exported to Excel for end user interpretation, distribution and feedback - not further analysis.

 

It's a good workaround you've given to export the data to txt using DAX Studio and then re-import it into Excel via Power Query but it would be a lot easier if MS just provided an 'Export to Excel' option in PBI Desktop !

Hi Paul,

sorry, completely misread this.

 

But not matter of the reasons, my answer was meant as an acknowledge that this feature is missing for some specific purposes.

 

However, there are workarounds at the moment (if DAX-studio doesn't work for you, you can use the R-feature within PBI as an alternative to export tables with a max. length of 100k rows).

 

Re descriptive statistics, I see no reason at all, to switch to a different tool. (Apart from the time you need to) Write your functions in M. Currently writing a blogpost about it, but here is a sneak-preview to give you a taste (Excel's TREND-function):

 

(YList as list, NoOfIntervalls as number) =>
let
    Source = Table.FromColumns({YList}),
    xAxis = Table.AddIndexColumn(Source, "Index", 1, 1),
    Rename1 = Table.RenameColumns(xAxis,{{"Column1", "y"}, {"Index", "x"}}),
    AvgX = List.Average(Rename1[x]),
    AvgY = List.Average(Rename1[y]),
    x = Table.AddColumn(Rename1, "xX", each [x]-List.Average(Rename1[x])),
    y = Table.AddColumn(x, "yY", each [y]-List.Average(x[y])),
    xy = Table.AddColumn(y, "xy", each [xX]*[yY]),
    xXx = Table.AddColumn(xy, "xXx", each [xX]*[xX]),
    a = List.Sum(xXx[xy])/List.Sum(xXx[xXx]),
    b = AvgY-(a*AvgX),
    ListIntervalls = {List.Max(Rename1[x])+1..List.Max(Rename1[x])+NoOfIntervalls},
    TableIntervalls = Table.FromList(ListIntervalls, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Rename = Table.RenameColumns(TableIntervalls,{{"Column1", "x"}}),
    Values = Table.AddColumn(Rename, "y", each [x]*a+b),
    TREND = Table.Combine({Rename1,Values})
in
    TREND

 

Big advantage of M is that you can do recursive operations as well, there are LOG-functions – so a pretty good basis to get going with descriptive statistics in Power BI.

 

Should find a space where we could share these codes within the community as well…

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

No worries, Imke. Thanks as always for your valuable insights. A workaround (in the absence of the required functionality in PBI Desktop) is better than no solution at all !  Paul

... just saw that our answers overlapped - anyway:

 

Anyone reading this and having coded some statistical functions already that she/he wants to share - please contact me 🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

jhaatue1
New Member

Looks like export to CSV is not available in BI any more. Can someone tell me why it was dropped.

Can  someone assist  I am in a report  view and would like to export to excel  .    The problem is the value of the calculated  fields in one of the coulumns in the report ( last years sales  using  calculate  and sameperiodlastyear function ) when exported to the excel sheet is not adding up .

 

However the simple  aggregrate field  for sum sales is adding up correctly .

 

If  there is a limitation on the ecport of calculated  measures , then how  do i ever report alot of  the reports  that are built  in Microsoft BI ?  Tableau allows you to export  the report straight  to  xcel  and  with calculated coulmns  as well.

 

Can anyone assist .

 

Colin 

 

 

still available on my June 2017 update version.  cannot export to .csv directly, only through a visualization table (bizarre I know).  There are also currently record limits on amount of records you are allowed to export.  this method is not ideal.

You can connect to the Power BI SSAS server if you know the localhost port.

I didn't read the whole thread so I don't know if @MattAllington chimed in at some stage but he has an excellent solution for this.  His post is about connecting to Power BI assets from exclel and if you read his code, you will see where the information you need is at.

Greg_Deckler
Super User
Super User

The export to CSV is there, you click the ellipses (...) on a visual and choose "Export". The only option you have for export is CSV. For anything beyond that, you'll have to post a new Idea.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Currently we have the export to csv or excel using the UI. If there is an api to do it programmatically, please let me know.

No, there is no way to do this.  The Power BI REST API lets you push new data to a data set, or pull metadata, but not the raw underlying data.  The export option (under the "..." menu for a visual) will export the data as the visual displays it, so if you visual aggregates the data in some way, the export will similarly be aggregated.  Lastly, analyze in excel allows you to connect to the data model in Power BI service, but you are working with a power pivot data model and not the underlying data, and you need to explicitly create measures in Power BI in order to drop them into your values section of the pivot table. 

    • Currently we have the export to csv,excel using the UI.
      Is there is an api to do it programmatically. Please let me know.

Hi

 

I have a dashboard in Power BI Service and i'm trying to publish to web and then export to CSV.
But there is no option to export to CSV from the Dashboards.

Could you tell me please if there are some way to do that.
Thanks in advance.

 

Pablo

Hi Pablo

 

Sorry for the delay in getting back to you. Firstly, most of my experience has been with PBI Desktop, not PBI Service. In addition, I have only been a member of this community for a relatively short period of time. So, I am by no means an expert. 

 

I dialed into my PBI Service account to have a look at what you have mentioned and agree there appears to be no option to export to CSV from the dashboards. The only place I can see you can do that is in the reports section. 

 

In the absense of any other members out there having a suggestion for you on that, it might be a good enhancement request to make to make on the Power BI Ideas site.

 

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.