Today, many organizations are stuck with manual processes for extracting and transforming data, which not only consumes time and resources, but also increases the risk of errors. In addition, the lack of technical knowledge needed to develop complex DAX queries and design efficient data models limits the ability of finance teams to delve deeper into analysis. Business managers often have to settle for static reports that do not allow them to simulate “what-if” scenarios or react quickly to market changes, while the fragmented view of financial information, due to reliance on disconnected systems, prevents them from having a single source of reliable truth.
The integration of Copilot and Power BI in Business Central offers a comprehensive solution to these challenges: thanks to intelligent automation through prompts, Copilot automatically generates the necessary queries and data models, dramatically reducing the learning curve. In turn, the visual power of Power BI facilitates predictive analytics and automatic forecasting without the need for advanced coding, and allows you to create interactive dashboards that support dynamic data exploration and instant scenario simulation. In this way, your organization unifies the source of truth, accelerates decision-making, anticipates risks and opportunities, and frees up the finance team to focus on delivering strategic value.
Introduction and objectives of the article
Today we will present the integration of Copilot and Power BI in Business Central as an advanced solution to enhance your predictive analytics and optimize financial decision-making. Throughout this section, we will define the scope and results you will obtain:
- Value of Copilot: You will understand how Copilot‘s artificial intelligence provides contextual suggestions, generates automatic DAX queries, and facilitates the creation of data models without the need for in-depth coding knowledge.
- Potential of Power BI: You will discover how to use Power BI Desktop and Power BI Service to visualize Business Central data, set up automatic updates, and share interactive dashboards with your team.
- Practical objectives: by the end, you will be able to:
- Connect Business Central to Power BI and correctly authenticate your credentials.
- Activate the Copilot Visual add-in and create effective prompts.
- Design a predictive dashboard with sales and cash flow forecasts.
- Publish and consume reports in Power BI Service, ensuring security and regulatory compliance.
With this approach, you will gain a comprehensive, real-time view of your organization’s financial health and have reliable forecasts that allow you to anticipate potential deviations.
Prerequisites and preparations
In this section, we will review the prerequisites and preparations you must complete to ensure a smooth integration of Copilot and Power BI in Business Central. Make sure you have all of the following before proceeding:
Required licenses
You must have Business Central Essentials or Premium, Power BI Pro (or higher), and access to Copilot Intelligence. Without these licenses, you will not be able to activate the connectors or take advantage of the advanced AI features.
Permissions and user roles
The user who configures the integration needs Business Central Administrator permissions to enable extensions and access to Azure AI Services, as well as being a member of a Power BI workspace with Member or Administrator permissions to publish and manage reports.
Test environment (sandbox)
We recommend using a Business Central sandbox to validate the configuration without compromising production data. Create or enable a test database with representative data that allows you to test queries and models before moving them to production.
Power BI connector for Business Central
Verify that you have the latest version of the official Business Central connector installed in Power BI Desktop. This will ensure compatibility with the latest entities and views in your tenant.
Access to Azure AI Services
Verify that your Microsoft 365 tenant has an Azure OpenAI service or Cognitive Services enabled for Copilot. Check that the Resource Group and keys are available for authentication from Business Central.
With these preparations complete, you are ready to move forward with enabling Copilot Intelligence in Business Central and connecting your data to Power BI.
Enabling Copilot in Business Central
To take full advantage of the power of Copilot, you must activate and configure it correctly in your Business Central environment. Below, I describe the steps I follow to enable Copilot Intelligence:
- Access the Business Central Admin Center
- I log in to https://admin.businesscentral.dynamics.com with a Global Administrator or Business Central Administrator account.
- I select the tenant and environment (production or sandbox) where I want to enable Copilot.
- Enable Copilot Intelligence
- In the side menu, I click on “Preview Features” (or “Funciones Preliminares” if your interface is in Spanish).
- I locate the “Copilot Intelligence” option and check the “Enable” box.
- Confirm and save the changes. Activation may take a few minutes while the AI services are provisioned.
- Assign roles and permissions
- Navigate to “Users” within the same Administration Center.
- Select the user or group of users who will use Copilot and click “Manage roles”.
- Add the role “AI User” (or similar defined in your tenant) and ensure they have the permission “AI Extensions: Read and Execute.”
- Verify the connection to Azure AI Services
- In Business Central, access “Copilot Configuration” in the search menu (type “Copilot Configuration”).
- I check that the Endpoint and Key for the Azure OpenAI or Cognitive Services service are correctly referenced.
- I perform a connection test: I click “Test Connection” and verify the “Connection Successful” message.
- Initial validation in Business Central
- I open any financial page (for example, General Ledger or General Journal).
- I click on the Copilot icon that appears in the ribbon.
- I type a simple prompt, such as “Show a summary of balances by account,” and confirm that Copilot generates the corresponding response or DAX query.
Copilot Intelligence will then be fully operational in your Business Central environment, ready to integrate with Power BI and begin exploiting its predictive capabilities.
Connecting Business Central to Power BI
To link your Business Central data to Power BI Desktop and start exploiting its analytical potential, follow these steps:
- Install and update Power BI Desktop
- Make sure you have the latest version of Power BI Desktop by downloading it from the official Microsoft website. This ensures full compatibility with the native Business Central connector and access to the latest features.
- Access the Business Central connector
- In Power BI Desktop, I click on Get Data and select Online Services. Then I choose Dynamics 365 Business Central and click Connect.
- Authentication via OAuth2
- The Microsoft Sign-in dialog box appears:
- I enter my corporate credentials (Office 365 account associated with Business Central).
- I confirm Power BI’s access permissions to my Business Central data, following the OAuth2 flow.
- When complete, Power BI displays a “Successful Authentication” message.
- Selecting the environment and company
- After authenticating, Power BI lists the available environments (Production, Sandbox, etc.).
- I select the environment where I have enabled Copilot.
- I choose the company that contains the test or production data I want to analyze.
- Choosing relevant entities and views
- Power BI displays the catalog of tables and views exposed by Business Central:
- I select the key entities for my dashboard, such as General Ledger Entries, Sales Invoices, Cash Flow Forecast, and Budget Entries.
- I can expand each entity to choose specific views (by date, account, or project) that optimize the volume of imported data.
- Data loading or transformation
- If I need a lightweight model, I click Load and let Power BI import the selected data directly.
- For more complex scenarios, I choose Transform Data to filter columns, change types, or apply Power Query before loading, improving the performance and quality of the model.
- Verifying the model in Power BI
- Once the data is imported, I review the Model view to:
- Confirm that the relationships between entities (for example, between Sales Invoices and Customers) are correctly configured.
- Adjust keys and cardinalities if necessary, setting the stage for Copilot Visual prompts.
With these steps completed, your Business Central data will be available and prepared in Power BI Desktop, ready to leverage the capabilities of Copilot in building predictive reports and advanced analytics.
Using Copilot in Power BI Desktop
To get the most out of Copilot‘s intelligence in Power BI Desktop, I follow these steps:
- Install the Copilot Visual add-in
- I open Power BI Desktop and go to the File > Options and Settings > Options menu.
- In the Add-ins section, I select Copilot Visual and click Enable.
- I restart Power BI Desktop so that the Copilot icon appears in the visualizations pane.
- Add the Copilot visual to the canvas
- In the Visualizations pane, I select Copilot Visual and drag it to the design area.
- I adjust the size of the card to allow enough space for interaction.
- Define the data context
- With Copilot Visual selected, in the Fields pane, I assign the key tables or columns (e.g., dates, accounts, or amounts) that Copilot will use as a reference.
- This allows automatic prompts to be based on the already defined data model.
- Write effective prompts
- In the Copilot text box, I write clear instructions, for example:
- “Generate a DAX measure that calculates the monthly percentage growth in sales.”
- “Create a six-month cash flow prediction model.”
- I always include the time horizon, the unit of analysis (customers, products), and the expected result (measure, table, or visual).
- In the Copilot text box, I write clear instructions, for example:
- Review and adjust the DAX code
- Copilot proposes the corresponding DAX query. I review it to verify table and field names.
- If necessary, I click Edit DAX to refine the logic (for example, adjust context filters or time intelligence functions).
- Refine AI parameters
- In the Copilot Settings tab, I adjust parameters such as Maximum Response Length, Level of Detail, and Context Sensitivity.
- This allows me to control how much autonomy Copilot has and tailor its suggestions to my needs for accuracy or speed.
- Visualize results and explore scenarios
- Once the DAX object or visual is generated, I combine Copilot’s output with line charts, pivot tables, or segmentations.
- I use the “What-if” feature to modify variables (growth, costs) and observe impacts in real time.
With these steps, Copilot Visual in Power BI Desktop allows you to translate your business needs into advanced queries and predictive models without manually writing a single line of DAX.
Creating a predictive report step by step
To illustrate how to combine Copilot and Power BI in the same report, I will follow this practical flow:
- Define objectives and KPIs
- First, I determine which metrics I want to forecast: monthly sales, cash flow, and gross margin. I open a new dashboard in Power BI Desktop and drag a blank canvas, giving it a title such as “Financial Forecast 2025.”
- Import basic visuals
- I insert line charts for historical sales and tables for account details. This serves as a basis before invoking Copilot Visual, as the model needs context references (dates, amounts, dimensions).
- Generate sales forecasts with Copilot
- I select Copilot Visual and in Fields I include the columns Date[Month] and Sales[Amount].
- In the prompt box I type:
“Create a DAX measure that forecasts monthly sales for the next 12 months using time series.”
- Copilot returns a formula such as:
- Sales_Forecast =
- FORECAST.ETS(
- SUM(Sales[Amount]),
- MAX(Date[Date]),
- 12,
- 0.05,
- TRUE
- )
- I accept and drag that measure to a new line chart, visualizing the historical series and the forecast range in dotted color.
- Incorporate cash flow analysis
- I repeat the process with Copilot Visual for cash flow:
“Generate a table that projects quarterly cash flow for the next year based on historical inflows and outflows.”
- Copilot creates a calculated table:
- ProjectedCashFlow =
- ADDCOLUMNS(
- CALENDAR(STARTOFMONTH(TODAY()), ENDOFYEAR(TODAY())),
- “EstimatedFlow”,
- [Inflows] – [Outflows]
- )
- I insert the table into the report and link it to a stacked column chart to distinguish between inflows and outflows.
- Add segmentations and “What-If” scenarios
- I add a What-if parameter to adjust the growth percentage (+/– 5%) and place it at the top of the report.
- I configure Copilot to recalculate the measures using the new adjustment factor when that parameter is modified, allowing me to explore impacts in real time.
- Customize format and details
- I apply conditional formatting to the tables (colors according to range of variation) and customize tooltips to show details for each point in the series.
- I rename the legends and axes in a consistent style and check that all text is legible and clear.
- Validate results and optimize performance
- I check that the forecasts make sense by comparing them with previous periods. I adjust the Copilot sensitivity parameters if I detect overfitting.
- To improve speed, I enable incremental loading of data in Power BI Service and filter the model to only the last 3 years for predictive calculations.
With this process, I achieve a fully integrated predictive report where Copilot handles the complexity of the formulas and Power BI provides all the visual and interactive capabilities to share it in Power BI Service and in Business Central itself.
Publishing and consuming reports in Power BI Service
To make your predictive reports available to the entire team and update them automatically, I perform the following steps:
- Publish the report to Power BI Service
- In Power BI Desktop, click File > Publish > Select destination.
- I choose the Power BI workspace where I want to host the report (for example, “Finance Analytics”).
- After a few seconds, I receive confirmation of “Successful publication” and a direct link to the report in the cloud.
- Configure data refresh (Scheduled Refresh)
- I access Power BI Service and open the workspace where I published the report.
- Under the Dataset section, I select the corresponding dataset and click Schedule refresh.
- I enable incremental refresh (if applicable) and set the frequency (daily at 2:00 a.m. in your time zone).
- I add the credentials for connecting to Business Central (OAuth2) to avoid authentication errors.
- Assign permissions and access roles
- In the same workspace, I go to Permissions > Manage Roles.
- I grant stakeholders the role of Reader or Member, depending on the level of interaction they require.
- If I want certain users to be able to edit the report, I add them as Members and define their scopes of work.
- Share and collaborate
- I use the Share option in the report to send a direct link to specific teams or users via email.
- Alternatively, I create a Power BI App that includes the report and publish it so that users see a centralized portal with all the financial information.
- Integrate the report into Business Central
- In Business Central, I navigate to “Page Design” on the Dashboard or Home page.
- I add a Power BI component and enter the report ID (URL) obtained from the Power BI Service.
- I configure the size and position of the tile so that your predictive dashboard appears natively within your Business Central environment.
With these steps, your report will be published, updated, and available both in Power BI Service and integrated directly into Business Central, facilitating immediate access to your forecasts and analyses from any device.
Examples of innovative use
To illustrate the potential of the synergy between Copilot and Power BI in Business Central, I will share three application scenarios that go beyond traditional use cases and can have a significant impact on your organization:
Proactive detection of spending anomalies
Imagine that, every month, Copilot automatically analyzes spending records and detects significant deviations from the budget.
Using a prompt such as “Identify out-of-range transactions or unusual patterns in operating expense accounts,” Copilot generates a DAX measure that flags vendors or categories with variations greater than 20%. In Power BI, a scatter plot highlights these points, allowing you to take action before an error or fraud becomes a bigger problem.
Forecasting stockouts based on customer behavior
Beyond simple inventory forecasting, I combine data from Business Central with information from CRM (Dynamics 365 Sales) and use Copilot to create a model that forecasts the probability of stockouts by product, customer, and region. The prompt “Generate a stockout risk score for the next 30 days based on sales history, delivery times, and active campaigns” produces a calculated table that assigns each item an alert index. In Power BI, a choropleth map shows geographic areas with the highest risk, facilitating smart replenishment decisions.
Simulation of “What-If” scenarios for mergers and acquisitions
For corporate finance teams, it is possible to design a dashboard that simulates the impact of a potential acquisition. With Copilot, I dynamically generate a combined model of two companies (using tables of revenues, costs, and expected synergies). The prompt “Merge the financial models of Company A and Company B and include a 10% savings for operational synergies” produces the necessary measures. Then, in Power BI, I apply “What-If” segmentations to adjust the savings percentage or the integration period and visualize in real time how the consolidated EBITDA varies.
Optimizing the project lifecycle with deviation analysis
In Dynamics 365 Project Operations environments integrated with Business Central, Copilot can generate a Sankey visualization showing the flow of actual versus budgeted costs by project phase. Using the prompt “Create a flow chart comparing planned and actual costs per task,” you get a series of measures and a data structure that feed into the chart. This allows the project manager to quickly identify phases with cost overruns and reallocate resources before deviations accumulate.
Conversational financial assistant within Teams
Finally, I incorporate the Power BI report into Microsoft Teams along with a Copilot bot that responds in natural language to questions such as “How did my margins evolve last quarter?” or “Show me customers with recurring orders and high growth.” Thanks to the Power BI Embedded API and Copilot’s AI layer, users without Power BI can access financial insights directly from Teams chat, fostering collaboration and data democratization.
The possibilities of Copilot and Power BI go far beyond generating simple reports: they enable the design of predictive, analytical, and conversational solutions that transform the way your company makes decisions and plans for the future.
Security and compliance considerations
In this section, I present the key guidelines for ensuring that your integration of Copilot and Power BI in Business Central complies with the highest standards of security and regulation. First, it is essential to protect sensitive data in transit and at rest. To do this, ensure that both Business Central and Power BI use TLS 1.2 or higher for their connections and that Azure databases are configured with Azure Storage Service encryption. In addition, enable Azure Key Vault to centrally manage Copilot keys and secrets, avoiding exposing credentials in plain text.
On the other hand, control access using Azure Active Directory (AAD), applying the principle of least privilege. Define custom roles in Business Central and Power BI Service, and consider enabling Privileged Identity Management (PIM) to elevate permissions only when strictly necessary. Log and audit all AI and visualization activities using Azure Monitor and Power BI audit logs, so you can identify unusual access or data exfiltration attempts.
In terms of regulatory compliance, review data retention and classification policies using Microsoft Purview or your preferred DLP solution. Verify that your data flows do not violate the GDPR or other local regulations by applying sensitivity labels and confidentiality labels to Business Central entities that contain personal or financial information. Finally, document your security configuration and perform regular audits to keep your contingency plan up to date and demonstrate compliance in the event of inspections.
Best practices and recommendations
In my experience, optimizing performance and ensuring the quality of your predictive analytics in Power BI with Copilot requires a combination of best practices in data sourcing, modeling, and continuous AI monitoring. Below, I share the recommendations I always apply:
To improve performance, I suggest filtering data from the source whenever possible, limiting the volume of rows and columns imported. In Power Query, it is more efficient to apply transformations (e.g., removing unnecessary columns or filtering historical periods) before loading the model. In addition, I take advantage of incremental loading in Power BI Service to update only new data, significantly reducing refresh times and server load.
Data quality is essential: before invoking Copilot, I always perform data cleaning and validation steps in Power Query. This includes standardizing date formats, removing duplicates, and checking for outliers or missing values. A model free of inconsistencies allows Copilot to generate more accurate prompts and avoids erroneous results in DAX formulas.
When using Copilot, I recommend defining clear and specific prompts, including the desired time horizon, metrics, and aggregation level. After each suggestion, I review the generated DAX code to adjust it (for example, by fine-tuning filters or time intelligence functions). I maintain a continuous feedback process: if I detect deviations or overfitting in the forecasts, I modify Copilot’s sensitivity parameters and regenerate the measure.
Finally, I monitor Copilot usage and activity logs in Azure AI Services to identify inefficient query patterns or potential bottlenecks. With this visibility, I adjust both the data model and prompts, ensuring that AI suggestions remain aligned with business objectives and established quality standards.