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.
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.
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 !
