Microsoft Dynamics 365 F&O and Microsoft Power BI integration

What moves between them

Data moves one way, from Microsoft Dynamics 365 F&O into Microsoft Power BI. On each scheduled run ml-connector reads finance and procurement entities from the OData service, vendor invoices, purchase orders, posted GL entries, vendors, and main accounts, and maps each one to a Power BI table such as Invoices, PurchaseOrders, GLEntries, Vendors, and GLAccounts. Accounting dimensions and cost centers travel as extra string columns on the relevant rows. Cadence is set by cron, commonly hourly or aligned to the close calendar. Because Power BI is a read-only analytics layer, the connector never writes report or dataset content back into the ERP.

How ml-connector handles it

Both systems sit behind Microsoft Entra ID and use the same client credentials grant, but each needs a different scope, so ml-connector requests one token for the Dynamics 365 F&O environment host and a separate token for the Power BI audience, refreshing each when a call returns 401. Power BI publishes no outbound webhook and Dynamics Business Event payloads are only stubs, so the connector polls OData on your schedule rather than relying on a push, and where a Business Event is enabled it uses the ControlNumber to know which record to pull. OData reads follow odata.nextLink to page through results, and entity keys must be fully specified, for example VendorsV2 keyed by VendorAccountNumber and dataAreaId, with cross-company set when more than one legal entity is in scope. Push dataset rows are append-only with no idempotency key, so ml-connector tracks a watermark and sends only net-new records, or does a delete-rows-then-repost full replace when a table needs to match the source exactly. Both sides return HTTP 429 with a Retry-After header, so the connector backs off and retries, and it stays within the Power BI ceilings of 10,000 rows per POST and 1,000,000 rows per hour per dataset. One setup gotcha: a tenant admin must enable service principals in the Power BI admin portal and add the service principal to the target workspace, since a service principal cannot reach My workspace.

A real-world example

A mid-sized industrial distributor with about 600 staff runs Microsoft Dynamics 365 F&O for procurement, accounts payable, and the general ledger across three legal entities. The finance team built their spend and AP aging dashboards in Power BI but refreshed them by exporting OData query results to spreadsheets every morning, which meant figures were always a day old and dimensions were re-keyed by hand. With Microsoft Dynamics 365 F&O connected to Microsoft Power BI, invoice, purchase order, and GL entry rows land in the dataset tables every hour with cost centers attached, scoped across all three legal entities. The dashboards now reflect current ledger activity and the manual export step is gone.

What you can do

• •Read invoices, purchase orders, posted GL entries, vendors, and main accounts from the Dynamics 365 F&O OData service.
• •Push those records into Power BI dataset tables as rows on the schedule you set.
• •Bridge the two Microsoft Entra ID token scopes so one client credentials flow serves both the ERP and Power BI audiences.
• •Track a watermark to send only net-new rows, or full-replace a table when it must match the source exactly.
• •Respect Power BI push limits and honor Retry-After on 429 responses from either system with automatic backoff.

Questions

Which direction does data move between Microsoft Dynamics 365 F&O and Microsoft Power BI?

Data moves one way, from Dynamics 365 F&O into Power BI. The connector reads finance and procurement entities from the OData service and appends them as rows to Power BI dataset tables. Power BI is a reporting destination that owns no transactional records, so nothing is written back into the ERP.

Does Power BI send webhooks when a dataset or report changes?

No. Power BI does not call out to an external webhook for refreshes, dataset changes, or report updates. The connector instead polls the Dynamics 365 F&O OData service on a cron schedule and pushes the results into Power BI. No inbound webhook handling is needed on the connector side.

How does the integration avoid duplicate rows in Power BI?

Push dataset row posts are append-only and Power BI offers no idempotency key, so duplicates are possible on naive re-runs. ml-connector tracks a watermark and sends only net-new records, or deletes all rows and reposts the full set when a table must match the source exactly. It also stays within the limit of 10,000 rows per POST and 1,000,000 rows per hour per dataset.