Say No to Export Data, Yes to Analyze in Excel: Power BI and Excel Can Talk!

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

22 thoughts on “Say No to Export Data, Yes to Analyze in Excel: Power BI and Excel Can Talk!

  • 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 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

  • 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

Leave a Reply

%d bloggers like this: