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.
Export Data
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.
Considerations
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
Summary
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.
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
In the considerations you mention “Analyze in Excel is supported in Excel 2020 SP 1 or later.” did you possibly mean to write Excel 2010? 🙂
haha! very good point and catch Darren. Yes, in 2010. Fixed now
Thanks 😊
Cheers
Reza
Hi Reza, an important limitation of Analyze in Excel is, that it requires the dataset to be in PBI Premium or that the user must have a PBI Pro licence. Might be good to add to you article for a well-rounded picture 😉
Hi Frank, Yes, the user either needs to be Pro user, or the dataset to be hosted in a Premium workspace. However, what other methods of sharing you use that is available without these? Only publish to web which is free. and of course, the analyze in excel is not available for that. So I don’t really call that a limitation unless you call sharing the Power BI report a limitation.
Cheers
Reza
Reza – Analyze in Excel seems to allow only building pivot tables from the PBI data set. What if users are looking for a “data dump” or tabular table and do not wish to see it in a pivot view?
Hi Mark
You can change the layout of the PivotTable in Excel to become a tabular layout as well
Cheers
Reza
Thanks for the insights Reza. Very useful.
What about that 🙂
https://exceleratorbi.com.au/excel-workbook-connection-local-power-bi-desktop/
Hi Thibault
The method Matt mentioned there is about connecting to the Power BI dataset hosted in Power BI Desktop. You can use DAX Studio or Power BI Helper to get the port and do the connection.
for the service though, the Analyze in Excel is a much easier option, and you won’t need anything extra
Cheers
Reza
Very good explanation!
Hi Reza
I thought I had read that Microsoft were not developing the Analyze in Excel any further. Is this still going to be a good for the future?
Thanks
Phil
Hi Phil
Analyze in Excel is just a connector, which is working perfectly fine. what further development this may need? all it needs to do it to connect to the Power BI dataset in the service, which is working fine.
Cheers
Reza
Hi Reza,
Interesting article!
To my best knowledge: Unfortunately the Admin Portal tenant settings do not allow you to prevent Export data (to Excel), and allow Analyze in Excel.
Do you perhaps have any thoughts for workarounds / other reasonable solutions?
Thanks,
Filip
Hi Filip.
You are right. At the moment. there is only one option that if you disable or enable it in the Tenant settings has an effect on both options at the same time. hopefully, these two would have their own setting option in the future.
In the meantime, I’d say best is educating the users and telling them the limitations of the export.
Cheers
Reza
Reza, the one thing I don’t get is we are trying to create repeatable apps, so in our case, we would have pre defined Power BI models and pre defined Analyze in Excel reports. Our users will be all Pro license so no issues at this stage. However, can I map an Analyze in Excel report from one dataset to another without having to recreate it ? Do I in effect edit the .odc file.
We are faced with the following decisions :
– If Analyze in Excel can work in the way described above then great
– If not we wlll need to replicate the Powerqueries in Excel workbooks and effectively build each model twice
If I understand you correctly;
you have an existing Excel file connected (using Analyze in Excel) to Power BI Dataset1, and now you want to point it to Dataset2, without re-doing all the things. It is possible. try an analyze in excel from dataset2 in the service. then in the new Excel file, go to connection properties, and copy and paste that into your old excel file. that’s it, this way, now you are connected to the dataset 2 from your existing excel file.
Cheers
Reza
Hi Reza,
One more question: as far as I understand, build permissions on the underlying dataset are necessary in order to use the analyze in Excel function.
However usually I don’t want to give build permissions to hundreds of users who are accessing my app. Also, as you state, row level security doesn’t apply to users with build permission (is “edit” and “build” the same?)
How does that fit together?
Regards, Ilja
Hi
They don’t need BUILD permission to use Analyze in Excel.
they need view access to the dataset, which can be provided by Apps, or normal sharing or any other methods without Edit rights.
Cheers
Reza
Hi, Reza, I have RSL set up for my powerBI, but when a user export data, the user can access all data. How I can make sure in PowerBI, the user can only export the data he can access, instead of the whole underlying data? Thanks!
Hi Mike
Does your user have EDIT rights on the dataset? if yes, then RLS doesn’t work for that user. this means a user with Administrator, Member, or Contributor role to the workspace.
if your user has only VIEW rights, then they should be able to only see their own data
Cheers
Reza
Hi Reza,
Great article and also used your parent-child one to implement RLS! As with previous comments, Microsoft docs and user testing I’m a bit confused on what permissions are needed to use Analyze in Excel, and how that behaves with RLS. I have a user that ‘analyze excel was greyed out’ in the report as a viewer in the workspace, but then pushing up to an app they could now use it?
Secondly, I’d like to make use of the connection string in Excel, to make a template Excel file for users to query with, rather than them just starting with a blank pivot table. However I seem to be getting the behaviour that RLS is enforced when the user the ‘analyze in excel’ function, but when I provide them with a Excel file with same connection to dataset they can see all data. Is this because I created the file so the inherit my permissions? Maybe this is something that gets cleared up when my tenant can use the ‘From Power BI datasets’ in Excel function.
Many thanks,
Patrick
Hi Patrick
regarding the Analyze in Excel case with the Viewer access that you mentioned. what is the Power BI license of that user? Power BI Pro? is the dataset located in the same workspace?
regarding the RLS issue with analyze in excel:
the thing is that Excel stores the data in itself. for example, if user1, connects to the dataset, and only get the data that he/she is authorized to see. then SAVE the file. the data at that point of the time saves in the excel file. if this file is now opened by user2, he/she might see that data that he/she not supposed to see. but if he/she REFRESHes the data, he/she would only see what he/she is authorized to see.
so it is more kind of an Excel problem as you see.
Cheers
Reza
Hello!
What happens if I share the excel file? Will people be able to have all functionalities?
Many thanks!
They need to have access to the underlying Power BI dataset, and if they do, yes, this will be the same Excel file
Hi Reza,
Well explained thank you. I have a Pro license and I still have an issue with the “Value” fields as non of my values (number, $, minutes) from my Pivot Table fields can be moved to the “Value” area?
Thank you
Hi Thierry
that has nothing to do with the license.
To get the fields in the value area you need explicit measures in your report. means measures that you create yourself in the Power BI file
Cheers
Reza
Hi Reza,
I face a problem in power bi underlying export data. when the user export the matrix visual then in excel those measures i used as a columns in visual, they are missing so plz can you help me on this topic. actually i look out all resources but still i am not able to solve so plz give me solution
This whole article was exactly about NOT to do the export. and use the Analyze in Excel. that would be the right way to do it.
Cheers
Reza