One of Power BI's most powerful features is its ability to combine data from multiple systems into a single report. Instead of exporting data from several applications and manually combining it in Excel, Power BI can connect directly to each source and bring the data together automatically. In this example, the report combines:
- Customer and invoice data from Business Central
- Opportunity data from Dynamics 365 CRM
The result is a single report showing both actual customer revenue and potential future revenue from open opportunities.
Getting started with Power BI Desktop
Power BI Desktop is available as a free download and provides robust functionality for building reports. While Power BI licenses are required for sharing reports with others, creating and developing reports in Power BI Desktop does not require additional licensing. The report-building process consists of two major phases:
- Retrieving data from source systems
- Building relationships and visuals using that data
To begin connecting data sources, open the Transform Data option in Power BI Desktop.
Using Power Query to connect data
The Power Query Editor is where connections to external systems are created and managed. Power Query allows you to retrieve, clean, and shape data before it is used in a report. Power BI supports connections to a wide variety of data sources, including:
- Microsoft Business Central
- Dynamics 365
- SQL databases
- Salesforce
- APIs
- Excel files
- On-premises systems
- Cloud-based applications
As a general rule, if data can be accessed through a database, API, or export, Power BI can usually connect to it.
Connect to Business Central
The first data source used in the example is Business Central. After authenticating and selecting the appropriate environment and company, two tables are imported:
Power BI provides a preview of the available data, and users can optionally clean the tables by:
- Removing unnecessary columns
- Renaming fields
- Creating calculated columns
- Simplifying the dataset
While extensive cleanup isn't required for every report, it can improve maintainability for reports that will be used long term.
Connect to Dynamics 365 CRM
Next, the example connects to Dynamics 365 CRM through Dataverse. Two tables are imported:
Power BI offers two methods for accessing Dataverse data:
Import downloads the data into Power BI and generally provides better performance and greater visual flexibility. DirectQuery retrieves data directly from the source each time it is needed, which can provide more current information but may affect performance and limit some reporting capabilities. For this example, Import mode is selected.
Load the data into Power BI
Once the queries are configured, selecting Close and Apply downloads the data from all connected systems into the Power BI model. After loading, the imported tables become available for reporting and analysis. At this point, the data exists inside Power BI, but the relationships between the tables still need to be defined.
Build relationships with the Power BI model
The Power BI model allows you to define how tables relate to one another without writing code. In this example:
- Business Central Customers connect to Invoices through the customer number.
- Business Central Customers connect to CRM Accounts through the account number.
- CRM Accounts connect to Opportunities through the account identifier.
Rather than creating formulas or performing lookups manually, Power BI uses these relationships to understand how records are connected across systems. Creating a relationship is as simple as dragging the matching field from one table to the corresponding field in another table. Once the relationships are created, Power BI can automatically combine information from multiple data sources.
Create reports without manual lookups
With the model in place, users can build visuals that combine data from different systems. For example, a table visual can display:
- Customer number
- Customer name
- Invoice totals from Business Central
- Estimated opportunity revenue from Dynamics 365 CRM
Because the relationships already exist, Power BI automatically finds the relevant records and combines them in a single visual. This eliminates the need for:
- VLOOKUPs
- Manual joins
- Excel exports
- Complex SQL queries
Power BI handles the connections automatically based on the relationships defined in the model.
Create interactive visualizations
The model also enables interactive reporting. In the example, a bar chart displays invoice revenue by month. Because the visuals share the same data model, selecting a customer in one visual automatically filters related visuals on the page. This allows users to explore data naturally and quickly answer questions such as:
- Which customers generated the most revenue?
- How much opportunity revenue exists for a customer?
- How has customer revenue changed over time?
All of these interactions occur automatically because Power BI understands how the data is connected.
Reduce manual reporting effort
By combining multiple data sources in a single model, Power BI can eliminate many of the repetitive reporting tasks organizations perform every day. Once a report is built:
- Data relationships remain intact
- Refreshing updates information from all connected systems
- Visuals update automatically
- Manual spreadsheet work is significantly reduced
Whether you're combining two systems or many, Power BI's data modeling capabilities provide a scalable way to bring information together and create reports that remain accurate, interactive, and easy to maintain.
