Analyze Business Central Insights Data in Excel
Analyze in Excel stands out as an exceptionally valuable feature, destined to be a favourite among all Excel users. Utilizing the Analyze in Excel feature, you can generate an Excel workbook that encompasses the complete semantic model of a particular Power BI report. This allows for the analysis of that semantic model in Excel through the use of PivotTables, Pivot Charts, and various other Excel functionalities.
There’s comprehensive online documentation about this feature. We recommend reading this article first.
Permissions required
To use Analyze in Excel, you must first have the correct permissions on the dataset, specifically, Build permissions enabled.
In your Power BI workspace just click the more options drop-down menu beside Semantic model. Then pick manage permissions. Once in that page then simply add the users and their relevant permissions.
It is also essential that each user has installed the Analyze in Excel update via the download menu on the top right of the of the workspace.
Note: Each user needs to have a Power BI Pro licence.
Option 1: Export data from a visual to Excel
You can export the data from an individual visual as an Excel file. In this example we’ll use the Total Sales table.
Open More options on the visual, select the three dots (…) in the top-right corner and choose Export Data from the drop-down menu
Pick an export type, you’ll see two choices:
Export with current layout: Gives you a like-for-like copy of what you see on screen, including totals, groupings, and basic formatting. Ideal for quickly sharing the exact view with colleagues.
Export summarised data: Exports the underlying aggregated data behind the visual in a clean, tabular format. Best for deeper analysis in Excel, e.g. building PivotTables, adding your own calculations, or combining with other datasets.
Click export and the visualisation will be exported as a .xlsx file you can open in Excel and from there you can configure your own reports.
Option 2: Analyze in Excel Web
In the Power BI service, open the Power BI App and select Export > Analyze in Excel. 
Power BI will create an Excel workbook. Once created it will prompt you to open in Excel Web. Select Yes to enable the Power BI query in Excel.
In Excel, you now have the capability to access the full semantic model, which includes all measures, tables, and fields. This enhancement enables you to effortlessly craft any type of report using a drag-and-drop interface, allowing for the straightforward selection of Filters, Rows, Columns, and Values.
Note: It’s also important to be aware the ability to drill into your underlying data isn’t available in the web version of Excel. To access this in-depth functionality, you’ll need to use the desktop version.
Option 3: Analyze in Excel Desktop
From the Excel workbook created in the web version, click Editing in the top-right corner and then Open in Desktop.
A further method to connect to the semantic models is to simply click: Data > Get Data
And in the drop-down: From Fabric and Power Platform > From Power BI. Use the right-hand panel to pick the relevant Business Central Insights app and semantic model. Click Insert PivotTable, then drag-and-drop fields to create your desired view.
After you have created your report. To access more granular, transaction-level detail, you’ll need to drill-through. Just double-click on any cell in the sheet.
Excel will open a new sheet showing the underlying records that make up that value. This gives you complete transparency and access to the most detailed level of your data, all while staying in Excel.
Another great advantage is that you only need to create the report once. Once you've created the report, you can save the Excel file, and the next time you open it, you can easily update data by selecting Data > Refresh.
If you notice that a specific field or measure is missing, or lacks drill-through functionality, please inform us. We value your feedback and will work to include it in future versions of the product.