Set up the Power BI Finance app
APPLIES TO: Generally available in Business Central 2024 release wave 2 (version 25.1).
This article explains how to set up the Power BI Finance app in Business Central.
For the Power BI Finance app to show correct KPIs and reports, do two things in Business Central:
- Set up G/L account categories for your chart of accounts.
- Map G/L account categories to corresponding categories used in the Power BI Finance semantic model.
Prerequisites
Before you configure the Power BI Finance app for Business Central, you must:
- Have a connector (AL) app that contains APIs, setup pages, and embed pages. Connectors are preinstalled with Business Central.
- Install the Power BI template app that contains a Power BI semantic model and Power BI reports for finance.
- Run the Connect to Power BI assisted setup guide.
To learn more, go to Install Power BI apps for Business Central.
Configure date filtering for the Power BI Finance app semantic model
This section describes how to configure date filtering for the Power BI Finance app. You can configure the app to load data based on the Start Date and End Date. This filter helps improve performance in your Business Central environment and reduce Power BI load times by only loading the data you intend to analyze.
Note
Configuring date filtering for the Power BI Finance app is optional, and is designed to help manage large datasets.
The Start Date and End Date filter can apply to the following tables:
- G/L Entries (related to the Income Statement)
- G/L Budget Entries
- Customer Ledger Entries
- Vendor Ledger Entries
Set up date filtering
- Search and open the Power BI Connector Setup page.
- Expand the Finance Report section.
- Fill in the Start Date and End Date fields under Income Statement & G/L Budget Entry Filters.
Repeat steps 2-3 for the date setup under Customer Ledger Entry Filters and Vendor Ledger Entry Filters.
The following fields are available on the Finance Report FastTab:
| No. | Data Area | Field Name | Description |
|---|---|---|---|
| 1 | Income Statement and G/L Budgets | Start Date | Specifies the starting date to apply to a range of income statement G/L entries and G/L budget entries. |
| 2 | Income Statement and G/L Budgets | End Date | Specifies the end date to apply to a range of income statement G/L entries and G/L budget entries. |
| 3 | Customer Ledger Entries | Start Date | Specifies the starting date to apply to a range of customer ledger entries. |
| 4 | Customer Ledger Entries | End Date | Specifies the end date to apply to a range of customer ledger entries. |
| 5 | Vendor Ledger Entries | Start Date | Specifies the starting date to apply to a range of vendor ledger entries. |
| 6 | Vendor Ledger Entries | End Date | Specifies the end date to apply to a range of vendor ledger entries. |
Set up G/L account categories for your chart of accounts
G/L account categories are groups of general ledger accounts. The categories are required for using the Power BI Finance app.
For each posting account in your chart of accounts, you must specify an Account Category and Account Subcategory. You don't need to set up the totaling accounts.
To learn more about how to work with G/L account categories, go to G/L account categories.
Map G/L account categories to corresponding categories used in the Power BI Finance semantic model
The Power BI Finance app has default mappings for G/L account categories in Business Central to corresponding values in the Power BI Finance semantic model. This mapping enables the finance KPIs and reports to show correct values.
The following sections show the default mappings. You can change the default values of the Business Central Category columns to your choice of G/L account categories by choosing the Power BI Account Categories action on the Power BI Connector Setup setup page or directly on the Power BI Account Categories page.
Level 1 Categories
Level 1 categories are the top-level categories without indentation.
| Power BI Account Category | Business Central Category |
|---|---|
| Assets (Level 1 Category) | Assets |
| Liabilities (Level 1 Category) | Liabilities |
| Equity (Level 1 Category) | Equity |
| Revenue (Level 1 Category) | Income |
| Cost of Goods Sold (Level 1 Category) | Cost of Goods Sold |
| Expense (Level 1 Category) | Expense |
Level 2 Categories
Level 2 categories are the second-level categories with indentation.
| Power BI Account Category | Business Central Category |
|---|---|
| Current Assets (Level 2 Category) | Current Assets |
| Current Liabilities (Level 2 Category) | Current Liabilities |
| Shareholder's Equity (Level 2 Category) | Common Stock |
| Interest Expense (Level 2 Category) | Interest Expense |
| Tax Expense (Level 2 Category) | Tax Expense |
| Extraordinary Expense (Level 2 Category) | Extraordinary Expense |
| FX Losses Expense (Level 2 Category) | FX Losses Expense |
| Depreciation and Amortization (Level 2 Category) | Depreciation and Amortization |
| Interest Revenue (Level 2 Category) | Interest Income |
| FX Gains Revenue (Level 2 Category) | FX Gains Income |
| Extraordinary Revenue (Level 2 Category) | Extraordinary Income |
| Fixed Assets (Level 2 Category) | Fixed Assets |
| Payroll Liabilities (Level 2 Category) | Payroll Liabilities |
| Long-term Liabilities (Level 2 Category) | Long Term Liabilities |
Level 3 Categories
Level 3 categories are the subcategories of Level 2 with indentation.
| Power BI Account Category | Business Central Category |
|---|---|
| Inventory (Level 3 Category) | Inventory |
| Accounts Payable (Level 3 Category) | Accounts Payable |
| Accounts Receivable (Level 3 Category) | Accounts Receivable |
| Purchases (Level 3 Category) | Purchases |
| Purchase Prepayments (Level 3 Category) | Prepaid Expenses |
| Liquid Assets (Level 3 Category) | Cash |
Troubleshooting
If you experience issues with the Power BI Finance app, the information in the following sections might help you get unblocked.
Getting the "Column Entry No. in Table G/L Entries contains a duplicate value" error?
When you refresh data from your Business Central environment into the Power BI Finance app, a refresh error might happen with the following error message:
Something went wrong
There was an error when processing the data in the dataset.
Please try again later or contact support. If you contact support, please provide these details.
Data source error: Column '<oii>Entry No.</oii>' in Table '<oii>G/L Entries</oii>' contains a duplicate value
Note
If you get this error, you're affected by a bug in the semantic model for the Power BI Finance app. We're working on a fix for this.
This error indicates that there are duplicate G/L entries in the Finance fact table in the semantic model. The error displays when we attempt to combine the three sub-queries (Balance Sheet G/L Entries, Income Statement G/L Entries, and Close Income Statement G/L Entries) into the single G/L Entries Fact table.
The current Balance Sheet G/L Entries query has a hard-coded filter transformation. This filter assumes that the source code for closing entries is CLSINCOME. This leads the model to allow closing entries to exist in both the Balance Sheet query and the Closing Entries query, resulting in duplicate entries.
Getting the "Column ‘G/L Account No.’ in Table ‘G/L Account’ contains a duplicate value" error?
When you refresh data from your Business Central environment into the Power BI Finance app, a refresh error might happen due to how your Begin-Total and End-Total accounts are configured in your Chart of Accounts.
If you receive this error, review the totaling accounts.
Something went wrong
There was an error when processing the data in the dataset.
Please try again later or contact support. If you contact support, please provide these details.
Data source error: Column 'G/L Account No.' in Table 'G/L Account' contains a duplicate value 'X' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
Why did this happen?
This error can happen because the Finance Connector app relies on the totaling accounts for hierarchy and indentation purposes.
Your totaling for an End-Total account must exactly reference both the Begin-Total and End-Total accounts. You can only use one Begin-Total account in one End-Total account.
Fix the totaling on your G/L Accounts
The following example shows how to set up the totaling accounts.
- A Begin-Total account of 1300 (Vehicles) and the End-Total account of 1390 (Vehicles, Total).
- The totaling defined for the End-Total is 1300..1390. Any other combination for your totaling account (such as 1300..1340, 1310..1390, or 1310..1340, etc.) can cause the error.
- One Begin-Total account can only be used in one End-Total account. If you use a Begin-Total account in two or more End-Total accounts, the Power BI Finance app can't match it to an End-Total account.
Related information
Installing Power BI apps for Business Central
Power BI finance app
Financial analytics overview
Finance overview