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.

16 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

Leave a Reply

%d bloggers like this: