6 Oct’17

BIExport Power BI DataSet to Excel

Introduction:

There are times when you may want to use Excel to view and interact with a dataset that you have Power BI for quick stretching of data slice and dice. Few people feel comfortable with excel PivotTable and PivotCharts, so by using Analyze in Excel, you can do just that, and access PivotTable, chart, and slicer features in Excel based on the dataset that exists in Power BI.

Supported Version and Requirements:

  • This feature support’s MS Excel 2010 SP1 and Later version.
  • Excel PivotTables do not support drag-and-drop aggregation of numeric fields. Your dataset in Power BI must have pre-defined measures.

Working:

1. Go to Power BI Workspaces and then go to datasets.

When you select Analyze in Excel from the menu (…) associated with a dataset or report in Power BI, Power BI creates an .ODC file and downloads it from the browser to your computer.

When you open the file in Excel, an empty PivotTable and Fields list appears with the tables, fields, and measures from the Power BI dataset. You can create PivotTables, charts, and analyze that dataset just as you would work with a local dataset in Excel. Dataset should have a pre-defined measures.

The .ODC file has an MSOLAP connection string that connects to your dataset in Power BI. When you analyze or work with the data, Excel queries that dataset in Power BI and returns the results to Excel. If that dataset connects to a live data source using DirectQuery, Power BI queries the data source and returns the result to Excel.

Analyze in Excel is very useful for datasets and reports that connect to Analysis Services Tabular or Multidimensional databases (because it provides a live connection to MSOLAP), or from Power BI Desktop files or Excel workbooks with data models that have model measures created using Data Analysis Expressions (DAX).

2. Open ODC file in Excel. Enable the security warning to access the dataset.

Note: Administrators for Power BI tenants can use the Power BI Admin Portal to disable the use of Analyze in Excel with on-premises datasets housed in Analysis Services (AS) databases. When that option is disabled, Analyze in Excel is disabled for AS databases, but continues to be available for use with other datasets. This is additional feature that provide security feature to your dataset.

Now that Excel has opened and you have an empty PivotTable, you’re ready to do all sorts of analysis with your Power BI dataset. Just as with other local workbooks, with Analyze with Excel you can create PivotTables, charts, add data from other sources, and so on. And of course, you can create different worksheets with all sorts of views into your data.

Easy to Share:

Once your workbook is saved, you can share it with other in your organization. When a user with whom you’ve shared your workbook opens the workbook, they’ll see your PivotTables and data as they appeared when the workbook was last saved, which may not be the latest version of the data. To get the latest data, users must use the Refresh button on the Data ribbon. And since the workbook is connecting to a dataset in Power BI, users attempting to refresh the workbook must sign into Power BI and install the Excel updates the first time they attempt to update using this method.

Since users will need to refresh the dataset, and refresh for external connections is not supported in Excel Online, it’s recommended that users open the workbook in the desktop version of Excel on their computer.

Written by

Jayant Patel

Sr. Software Developer

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Reading more then 10K records in D3FOE OData API

    11 July’ 2018

    Introduction: We all know Dynamics 365 Finance and Operations has limitation of 10K records to be fetched at a time usi...

    Read more
  • Paging in D365 Customer Engagement v9.0

    10 July’ 2018

    Introduction: The Xrm.retrieveMultipleRecords method is used to retrieve a collection of records in Dynamics 365 Custom...

    Read more
  • Set up Dynamics 365 connection in Microsoft Social Engagement

    10 July’ 2018

    Introduction: This blog explains how to Set up Dynamics 365 connection in Microsoft Social Engagement. Steps to be follo...

    Read more
  • Voice of the Customer failed to install

    10 July’ 2018

    Introduction: Many people face issues in installing Voice of Customer solution on v9 environment and trying repeatedly ...

    Read more
  • Scribe Insight AX as a Web Service Find Block issue

    10 July’ 2018

    Introduction: If we need to look up for any value from AX then we do it by using a Find Block in Scribe Insight Eg: Basi...

    Read more