Reporting

How to combine Dynamics 365, SharePoint and Excel data in Power BI (and when to go further)

By the Trigno team · 27 October 2026 · 8 min read

If your customers live in Dynamics 365, your project tracker is a SharePoint list and finance works from Excel, you have probably asked: can we just see all of this in one report? Often you can, in Power BI. Here is how, and how to tell when you have outgrown that approach.

Step 1: connect each source

• Dynamics 365: use Power BI's Dataverse connector. Pick only the tables and columns you need, such as accounts, opportunities and invoices.
• SharePoint lists: use the SharePoint Online List connector with your site address. Lookup and person columns come through as records you will need to expand.
• Excel: store the workbook in SharePoint or OneDrive, not on a laptop, and connect to it there so scheduled refresh works.

Step 2: decide what joins the data together

This is where most projects stall. To combine sources you need a shared key: an account number, a project code or a company registration number that appears in each system. Customer names are a poor key: “Halden Ltd”, “HALDEN LIMITED” and “Halden (old)” are the same customer to a person, but three different customers to Power BI.

Tip: Before building anything, take 20 records and check by hand whether you can match them across all three systems. If you can't, Power BI can't either.

Step 3: shape and merge in Power Query

1. Clean the key in every source: trim spaces, use consistent case, remove prefixes.
2. Create one “master” list, for example all customers, with one row per key.
3. Merge the other tables onto it and keep the columns you need.
4. Build a simple star-shaped model: the master list in the middle, activity tables (orders, projects, invoices) around it.

Step 4: schedule refresh and share

Publish to the Power BI service, set a refresh schedule and share through a workspace or app. Power BI Pro licences allow several scheduled refreshes a day, which is enough for most management reporting.

Signs you have outgrown “everything in Power BI”

• Refreshes are slow or fail because each report pulls and reshapes raw data itself.
• Different reports show different numbers because each one applies its own logic.
• You need to correct data, not just view it, and the corrections get lost in the next refresh.
• You want an audit trail of who changed what, or to add AI over your data.

At that point the better design is a central database that brings the sources together once, matches records to one ID, flags conflicts for someone to fix, and keeps history. Power BI then reads clean, agreed data, and every report shows the same number. That is what we mean by a single source of truth.