Power BI + Dynamics 365: Finance & Operations and CRM Guide

Short answer: there is no single "Power BI connector for Dynamics 365", because Dynamics 365 is several products that keep their data in different places. The customer engagement apps (Sales, Customer Service, Field Service) live in Microsoft Dataverse, so Power BI reads them with the Dataverse connector. Dynamics 365 Finance and Supply Chain Management (together, "finance and operations apps", or F&O) keep their data in their own database: you report on it through the embedded Power BI reports built on the Entity store, and you copy it out for your own analytics with Azure Synapse Link for Dataverse or Link to Microsoft Fabric. Business Central has its own connector and nine ready-made Power BI apps.

One date matters more than anything else on this page. Microsoft's list of removed and deprecated F&O platform features states that Export to Azure Data Lake "retired on November 1, 2024, and will permanently stop on November 30, 2026. After the service stops, it won't export new or changed data", and that Microsoft "won't grant extensions after November 30, 2026" (Microsoft Learn, read 2026-10-15). An earlier version of this guide recommended Export to Data Lake for large Finance workloads. That advice is withdrawn. If any of your Power BI reports read from it, the section Moving off Export to Data Lake below is the part to act on.

This guide is for finance, IT and BI leads who run Dynamics 365 and need to decide how Power BI should get its data. Every Microsoft behaviour it describes links to the Microsoft Learn page it comes from, with the date we read it.

---

Which Dynamics 365 app are you connecting?

The F&O row is where most of the mistakes happen, because the obvious approach (point Power BI at the database) is not available. Microsoft's BYOD article is explicit: "The application doesn't allow T-SQL connections to the production database" (Microsoft Learn, read 2026-10-15).

If your estate spans more than one of these apps, our Power BI consulting services can help you choose a route for each.

---

Power BI for Dynamics 365 Finance and Operations: the four paths

Path 1: the embedded reports and the Entity store (start here)

The Entity store is "an operational data store that's included with Microsoft Dynamics 365 Finance", built for reporting with SQL Server's clustered columnstore indexes. Microsoft says customers "can use Microsoft Power BI DirectQuery models together with Entity store to enable high-volume, near-real-time analytical reporting", and that because no data is cached in the Power BI service, "data doesn't leave your environment" (Power BI integration with Entity store, read 2026-10-15).

Three practical points from the same article:

• Freshness. The staged pattern suits reports where "data freshness of approximately 10 minutes is acceptable". Real-time aggregate measurements exist for embedded charts and KPIs that must react within seconds.
• Refresh is managed for you. The system automates Entity store refresh. Administrators find it under System administration > Setup > Entity Store.
• Direct database access is for diagnostics. You can connect to the Entity store database directly only from a related sandbox environment, for troubleshooting.

Microsoft's own guidance is to start with what ships: "The default operational reports take advantage of embedded Power BI and Entity store. Use the default operational reports as your first choice. You can also extend the ready-made operational reports to meet your requirements" (BYOD article, read 2026-10-15).

Use this path when the reporting lives inside F&O workspaces, the people reading it are F&O users, and you do not need to join F&O data with other systems.

Path 2: Azure Synapse Link for Dataverse (your own data lake)

This is Microsoft's replacement for Export to Data Lake. It lets you "continuously export data from finance and operations apps into Azure Synapse Analytics and Azure Data Lake Storage Gen2", covering "both standard and custom finance and operations entities and tables", with create, update and delete transactions replicated (Choose finance and operations data in Azure Synapse Link, read 2026-10-15). F&O tables are saved in Delta Lake (Parquet) format by default.

What you need, from the same page:

• App version. Generally available on 10.0.36 (cumulative update 7.0.7036.133 or later), 10.0.37 (7.0.7068.109 or later) and 10.0.38 (7.0.7120.59 or later). Later fixes, such as array fields and expired exchange-rate rows, need later updates.
• Environment. A finance and operations sandbox (Tier-2) or higher, or an environment provisioned with an ERP-based template.
• Azure resources. To query in Synapse: a data lake, an Azure Synapse workspace and a Spark pool. To feed your own data warehouse: a storage account only, with incremental changes written as CSV files.
• A new profile. You can't add F&O data to a storage account already configured with Azure Synapse Link, so plan a new Synapse Link profile.

Cost you control. The Delta conversion interval (5, 15 or 60 minutes, or 1,440 for daily) sets how often Spark runs: "The lower the time interval, the more compute resources are consumed and you can incur more cost." Pick the interval the report actually needs. A month-end pack does not need 5-minute data.

Use this path when you want F&O data in storage you own, joined with other systems, modelled your way, and kept for as long as you decide.

Path 3: Link to Microsoft Fabric (no storage of your own)

Link to Fabric makes Dynamics 365 data, including F&O data, available in OneLake without an export pipeline. Microsoft describes it this way: "With shortcuts from Dataverse directly into OneLake, your data stays in Dataverse while authorized users get to work with data in Fabric." The system creates a Fabric Lakehouse, a SQL endpoint and a Power BI semantic model for you, and keeps them updated as the data changes (Link your Dataverse environment to Microsoft Fabric, read 2026-10-15).

The trade-offs Microsoft lists:

• Storage. It needs no storage account or Synapse workspace, but tables added to OneLake "consume Dataverse storage", which shows in the Power Platform admin center.
• Scope. Today a Dataverse environment links to a single Fabric workspace.
• Fabric capacity. You need a Microsoft Fabric workspace.
• Using Synapse Link as well. An existing Synapse Link profile can also feed Fabric if you enable "Delta parquet conversion for Fabric link". That gives you Power BI Direct Lake reports over your own storage.

Use this path when your organisation already runs Fabric capacity, or plans to, and you would rather pay in Dataverse storage than run your own lake and Spark pool.

Path 4: BYOD and OData (specific cases)

Bring your own database (BYOD) exports data entities into your own Azure SQL database, with full or incremental pushes on a batch schedule; Microsoft lists "more than 1,700 data entities". It recommends BYOD when "you must export data into your own data warehouse", when "you use analytical tools other than Power BI, and those tools require T-SQL access", or for batch integration with other systems. For production it sets minimum database tiers (for example Standard S6, General Purpose GP_Gen5_8), and composite entities can't be exported (BYOD, read 2026-10-15).

OData feeds on data entities still work for Power BI reports ("this approach is still supported", per the Entity store article). For high volumes, the same article points you to Entity store DirectQuery, and the two lake paths above exist for scale. In practice, keep OData for small reference data rather than transaction history.

F&O path comparison

---

Moving off Export to Data Lake before November 30, 2026

If your Power BI datasets, dataflows or pipelines read the CDM folders that Export to Data Lake writes, they stop receiving new or changed data when the service stops. Files already exported stay where they are. Microsoft publishes a transition guide from the legacy data services. The working order we recommend is:

1. Inventory every consumer of the lake. Power BI semantic models, Data Factory or Synapse pipelines, notebooks and any third-party tool reading the folders. The reports that break silently are the ones nobody remembers connecting.
2. Choose the destination. Azure Synapse Link if you keep your own storage (Delta for analytics, or the incremental CSV folders if a downstream warehouse expects change files), or Link to Fabric if you are moving to Fabric.
3. Check versions and tables. Apply the cumulative updates listed above. Custom tables and data entities only appear once row version change tracking is enabled for them.
4. Plan for the behaviour changes Microsoft documents (read 2026-10-15):
   • Deleted rows stay in the export. Synapse Link keeps rows deleted in F&O and flags them with IsDelete, where Export to Data Lake removed them, so filter them out in Power Query or in the view.
   • Renamed columns. F&O ID fields arrive as FnO_Id, and fields named after SQL reserved words get a trailing character (Level becomes Level_).
   • Truncated text. Memo fields of type nVarchar(Max) are cut to 2,000 characters.
   • Derived tables lack base fields. A derived table such as DirPerson does not include the fields of its base table, so select the base table too.
   • Some rows refresh daily. Kernel tables such as DATAAREA and USERINFO update every 24 hours, not in near real time.
5. Rebuild the semantic models on the new source and run both in parallel. Do not switch off the old reports until the new ones reconcile: trial balance by period and company, open receivables and payables, and inventory value. Those three totals catch most mapping errors.
6. Re-apply security in Power BI. Data in your lake no longer carries F&O role security. The same Microsoft page notes that the Synapse Link service applies a bypass for extensible data security policies, so company, legal-entity and business-unit restrictions have to be rebuilt as Power BI row-level security (below).

---

Power BI for Dynamics 365 Sales and Customer Service (Dataverse)

The Dataverse connector is the native route for every Dataverse-based Dynamics 365 app. It reads standard and custom tables and supports both Import and DirectQuery in Power BI semantic models (Power Query Dataverse connector, read 2026-10-15). Know its limits before you design on it. Microsoft's connector page says:

• Admin setting. The environment's TDS endpoint setting must be enabled.
• Speed. Most default tables are retrieved at "approximately 500 rows per second".
• Timeout. Any single query has "a fixed five (5) minute timeout".
• Bulk extraction. The connector is "meant to serve analytical scenarios where data volumes are relatively small", and "the recommended approach for bulk data extraction is Azure Synapse Link".

Setup

1. In Power BI Desktop: Get Data > Dataverse.
2. Enter the environment URL without https:// or the trailing slash, for example yourorg.crm.dynamics.com.
3. Sign in with your Microsoft Entra ID account. A service principal is supported for Power BI and Fabric, but not through an on-premises or VNet data gateway.
4. Choose Import or DirectQuery, select the tables you need, then shape them into a star schema rather than loading them as they are.

In Power Query M, the same connection looks like this:

let
    Source = CommonDataService.Database("yourorg.crm.dynamics.com"),
    Opportunity = Source{[Schema = "dbo", Item = "opportunity"]}[Data]
in
    Opportunity

Key tables for Sales and Service analytics

account           Customer and prospect organisations
contact           Individual contacts
opportunity       Deals with stage, probability and close date
quote             Price quotes linked to opportunities
salesorder        Confirmed sales orders
invoice           Invoices from orders
lead              Leads before qualification
incident          Customer service cases
activitypointer   All activities (emails, calls, meetings)
systemuser        Users and their business units
businessunit      The business unit hierarchy (used for security)

Choice columns: map codes to labels, and check custom values

Choice (option set) columns store integers, so reports need the labels mapped in. Check the codes against your own environment before hard-coding them, because organisations add custom status reasons. The default opportunity status reasons are:

AddLabel = Table.AddColumn(Opportunity, "Status Label", each
    if [statuscode] = 1 then "In Progress"
    else if [statuscode] = 2 then "On Hold"
    else if [statuscode] = 3 then "Won"
    else if [statuscode] = 4 then "Canceled"
    else if [statuscode] = 5 then "Out-Sold"
    else "Other")

Reports inside Dynamics 365

Model-driven Dynamics 365 apps can show Power BI tiles on dashboards and embed Power BI reports in forms once Power BI embedding is enabled for the environment, and an embedded report can be filtered to the record that is open. Viewers need Power BI access to the workspace the report is published in, so settle licensing before you design the embed.

---

Power BI for Dynamics 365 Business Central

Business Central is the simplest of the three. Microsoft ships Power BI apps for nine functional areas: Finance, Sales, Purchasing, Inventory, Inventory Valuation, Projects, Manufacturing, Subscription Billing and Sustainability. Each app includes "a semantic model with star schemas that support the reports, as well as KPIs implemented as measures", and the reports can be opened from inside Business Central (Power BI apps for functional areas, read 2026-10-15).

The Finance app alone covers financial overview, income statement and balance sheet by month, budget comparison, liquidity KPIs, profitability, EBITDA, aged receivables and payables, and general ledger entries by all eight shortcut dimensions. Install the apps before you build anything custom, and use Microsoft's documented semantic models as the reference when you extend them.

---

Cross-module analytics: Sales, Service and Finance together

The reason most organisations want Power BI over Dynamics 365 is to combine data the apps keep apart. Once F&O data is in a lake (Path 2 or 3), a customer view can join Dataverse and Finance data:

The join key is the hard part. Dataverse accounts and F&O customers are separate records unless dual-write or another integration keeps them linked, so agree the mapping table before building measures. Two sales-to-cash measures with the correct default codes:

// Quote win rate: quote statecode 2 = Won (0 Draft, 1 Active, 3 Closed).
// Closed quotes carry status reason 5 Lost, 6 Canceled or 7 Revised. A revised quote was
// superseded by a new revision, not lost, so it stays out of the denominator.
Quote Win Rate =
DIVIDE (
    CALCULATE ( COUNTROWS ( Quote ), Quote[statecode] = 2 ),
    CALCULATE (
        COUNTROWS ( Quote ),
        Quote[statecode] = 2 || ( Quote[statecode] = 3 && Quote[statuscode] <> 7 )
    )
)

// Average days from opportunity creation to close, won deals only
// (opportunity statecode 1 = Won)
Avg Sales Cycle Days =
AVERAGEX (
    FILTER ( Opportunity, Opportunity[statecode] = 1 ),
    DATEDIFF ( Opportunity[createdon], Opportunity[actualclosedate], DAY )
)

---

Security: rebuilding Dynamics 365 access rules in Power BI

Microsoft notes that DirectQuery over Dataverse "can also more strictly enforce the Dataverse security model" (Dataverse connector for Power BI). An imported model, or any model built on lake data, holds the same rows for everyone who opens it. You need row-level security (RLS) in the semantic model.

A common Dataverse pattern restricts users to their own business unit and every unit below it. Add a path column to the business unit table:

BusinessUnitPath =
PATH ( BusinessUnit[businessunitid], BusinessUnit[parentbusinessunitid] )

Then use this RLS filter on the BusinessUnit table, related to your facts through owningbusinessunit:

VAR UserBusinessUnit =
    LOOKUPVALUE (
        SystemUser[businessunitid],
        SystemUser[internalemailaddress], USERPRINCIPALNAME ()
    )
RETURN
    PATHCONTAINS ( BusinessUnit[BusinessUnitPath], UserBusinessUnit )

For F&O data, the usual equivalent is a filter on legal entity (DataAreaId), driven by a user-to-company mapping table that you maintain from F&O security assignments. Test every role with "View as" in Power BI Desktop before publishing.

---

Frequently Asked Questions

What is the best way to connect Power BI to Dynamics 365 Finance and Operations?

Start with the embedded Power BI reports on the Entity store. Microsoft calls the default operational reports "your first choice". For analytics you own, or for joining F&O with other systems, use Azure Synapse Link for Dataverse (your own storage) or Link to Microsoft Fabric (Dataverse storage, shortcut into OneLake). Use BYOD only when another tool needs T-SQL access.

Is Export to Azure Data Lake still supported?

No. Microsoft retired it on November 1, 2024, and it permanently stops on November 30, 2026, after which it exports no new or changed data. Microsoft has said it will not grant extensions. Its replacement is Azure Synapse Link for Dataverse, which also feeds Microsoft Fabric.

Can Power BI connect directly to the Dynamics 365 Finance production database?

No. Microsoft states that the application does not allow T-SQL connections to the production database. Use the Entity store, a Synapse Link or Fabric link export, or BYOD instead.

Do I need Microsoft Fabric to use Power BI with Dynamics 365?

No. The embedded F&O reports, the Dataverse connector, Azure Synapse Link to your own storage and BYOD all work without Fabric. Fabric becomes useful when you want Microsoft to manage the lake for you (Link to Fabric) or want Direct Lake reports.

What changes when we move from Export to Data Lake to Azure Synapse Link?

Deleted rows are kept and flagged with IsDelete instead of removed, ID fields are renamed FnO_Id, long memo fields are truncated to 2,000 characters, and derived tables don't include their base table's fields. Data in the lake also no longer carries F&O security, so row-level security has to be rebuilt in Power BI.

Does Power BI work with Dynamics 365 Business Central?

Yes. Business Central has its own Power BI connector and nine Microsoft Power BI apps (Finance, Sales, Purchasing, Inventory, Inventory Valuation, Projects, Manufacturing, Subscription Billing and Sustainability), each with a documented semantic model and KPIs.

---

Next steps

If Export to Data Lake still feeds any of your reports, the deadline is November 30, 2026. The work is mostly inventory, a parallel run and reconciliation, and it is much easier before the old feed stops than after.

ECOSIRE builds Power BI models on ERP data, and our Power BI ERP integration service covers Dynamics 365. The first step is a data audit that maps which of the paths above fits your environment and licences. For the wider picture, see our guides to connecting Power BI to ERP systems, Microsoft Fabric vs Power BI and Power BI financial reporting dashboards, or contact our analytics team.