The Analyze in Excel feature from Power BI service allows end-users to connect to Power BI datasets through Excel. When the Power BI dataset and Excel connection has been made, end-users can perform analysis using PivotTables. Before connecting to a Power BI dataset, the end-user must download the Analyze in Excel Add-in from Power BI service.
To download the Excel add-in, sign in to Power BI service, navigate to the ellipses in the top right corner of your screen home screen, hover over the “Download” section, and select the “Analyze in Excel Updates” button. After your Excel add-in has finished downloading, Power BI detects the Excel version on your machine and downloads the correct version of Analyze in Excel.
To utilize this Analyze in Excel add-in, navigate to the workspace and dataset you want to run analysis against and select the Analyze in Excel option associated with the report or dataset.
After selecting the Analyze in Excel option, Power BI generates an Excel file connected to the related dataset. The Excel file title will match the title corresponding to the dataset or report. Open this file to begin building your analysis with PivotTables. The Excel file runs on an MSOLAP connection. The MSOLAP connection to your dataset in Power BI queries the Power BI Service dataset anytime you work within the excel file to ensure your information is always up to date.
All DAX measures and custom columns within your dataset should pass over to the Excel file. The first time you open this file, Excel will prompt the end-user to sign in to Power BI service and authenticate the analyst’s identity. This workbook can be saved like any other Excel file and shared with other users within your organization that have build access against the Power BI dataset.
All Analyze in Excel updates will be passed through Microsoft Excel and not Power BI. If your analysis produces errors after updating your current Excel version, Microsoft recommends that you re-download the “Analyze in Excel Updates” from Power BI Service.
Now that you have installed the “Analyze in Excel Updates” from Power BI service, you can take advantage of the integration between your Power BI datasets and Excel. Please enjoy your new analytical process and information-sharing capability.