Forum Discussion
Exporting data from Power BI to Excel
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.
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.
