Despite the fact that I have explained about the Analyze in Excel feature in Power BI before, still, I hear and see many users are using the Export data option in Power BI much more. In this post, I am explaining what is the difference and why it is better to use Analyze in Excel rather than exporting data from Power BI.
In Power BI visuals, you can use the ability to export data. Export data will save the data in CSV or Excel format, you can choose from the underlying data or summarized data.
This is an example of export data;
Analyze in Excel
Analyze in Excel is a feature on Power BI datasets and reports published to the service. You can find it right in front of every report’s name as an action;
Or even inside a report, you can choose the Analyze in Excel
The Analyze in Excel option in Power BI service will download an ODC file (ODC stands for Office Data Connection). ODC file can be opened with Excel.
When you open the file in Excel, you will be asked to enable the connection. The reason for the question is that you are connecting to a data source in the cloud.
If this is the first time you are opening this, you may be asked to log in. Use the same Power BI account username and password that you had access to the report from it. After successful sign in, you should see a PivotTable with data tables and fields fetched from Power BI model.
Drag data fields into the slicing and dicing area (right under the fields pane), and you will see a result coming up in PivotTable. This result is fetched live from the Power BI model in the Power BI service.
Why Analyze in Excel is Better?
There are many reasons, why the Analyze in Excel is a better option than Export data. Let’s explore some of those here.
Analyze in Excel is Live Connection, Export Data is Offline, outdated
As soon as you export data from a Power BI report or dataset, you create a snapshot of the data at that point in time. If the report refreshes the next day, your exported data is no longer up-to-date. It is offline and outdated.
However, when you use Analyze in Excel. Excel’s data is fetched live from the Power BI dataset in the service. You can see that in the connection properties of Exel.
You can check the connection properties in the Data tab, under Connections, Properties section.
The Connection properties will have an Azure address in it with the ID of the dataset in the Power BI service. You can even use this connection in any other excel file to connect to the same dataset.
Export Data is limited to 150,000 Rows, Analyze in Excel Not!
Export data has a limitation on the number of rows. Analyze in Excel doesn’t. You are connected to the model live. It looks like a live connection to SSAS tabular, you can do whatever you want with the data.
The export is limited to 150K rows if you export to Excel, and 30K rows for CSV files.
Export Data is limited to specific visual and fields used in that visual
When you use Export data, you are only doing it from a specific visual. and fields in that visual (or related fields to that) are exported.
When you use analyze in Excel, you will have access to all the tables, columns and their calculated fields and measures in Excel:
Analyze in Excel is the Entire model: all tables and calculations
Export Data is not Secure. Analyze in Excel uses Power BI account credentials
You might export data using an account that has access to everything. and then share the exported data file with someone who should NOT have access to the data. Because the security and the data are decoupled when you used the export data, then there is no security around it. you have to be careful of securing the exported data yourself.
However, when you use Analyze in Excel, You have to log in with your Power BI account, and you will see the data only if you have access to it.
Analyze in Excel supports Row-Level Security
Because users log in from the Excel when they use the Analyze in Excel feature, then they will see only part of the data that they are allowed to see. That means if there is row-level security enabled on the data. Users will see only their part of the data, nothing more. (as long as they don’t have Edit access on the dataset)
Analyze in Excel uses the concept of Shared Datasets
When you use the Analyze in Excel feature, you are using the shared dataset of Power BI from the Excel front end. whenever the dataset refreshes, you have the new data. If the dataset gets a new table or field, you can access that too.
There are some points to consider when you use Analyze in Excel;
- Analyze in Excel works perfectly fine for App users too.
- Having explicit measures will make the Analyze in Excel experience much more convenient.
- Analyze in Excel can be also achieved using the Power BI Publisher for Excel.
- Analyze in Excel is supported in Excel 2010 SP 1 or later.
- Analyze in Excel is only available for the reports published to the Power BI service at the moment
There are so many benefits in using Analyze in Excel feature than the Export Data. some highlights of those benefits explained in this post. I strongly recommend you to use Analyze in Excel moving forward to analyze your Power BI data in Excel and encourage others to do the same. Power BI and Excel can talk in other ways together too, read my other article about it.