cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kcantor Super Contributor
Super Contributor

Re: Exporting data from Power BI to Excel

@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 Datanaut!




Sean Super Contributor
Super Contributor

Re: Exporting data from Power BI to Excel

@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 Super Contributor
Super Contributor

Re: Exporting data from Power BI to Excel

@Sean

I have added votes.



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

Proud to be a Datanaut!




ryans Regular Visitor
Regular Visitor

Re: 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.

Sean Super Contributor
Super Contributor

Re: Exporting data from Power BI to Excel

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

 

 

 

 

Paulx99 Member
Member

Re: Exporting data from Power BI to Excel

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.

 

 

 

 

Paulx99 Member
Member

Re: Exporting data from Power BI to Excel

Thanks Sean.

Paulx99 Member
Member

Re: Exporting data from Power BI to Excel

Agree.

Super User III
Super User III

Re: Exporting data from Power BI to Excel

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.

 

 

 

 

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

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Paulx99 Member
Member

Re: Exporting data from Power BI to Excel

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.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors