Did you know that the Business Central connector in Power BI supports related tables when using APIs?

Recently the Power BI connector in Power BI Desktop allows you to harvest the power of Business Central APIs:

In the Standard APIs folder you can see all APIs Business Central publishes automatically:

API(V2.0) for Dynamics 365 Business Central – Business Central | Microsoft Docs

And in the Advanced APIs you can find the APIs you created/published:

In the Advanced APIs there are also some very interesting admin and automation APIs, but I’ll discuss them in a later post.

Let’s have a look at a simple example, Customers:

When you scroll to the right in the Customers API you can see columns containing records, for example: countryRegion, currency, paymentTerm,… Now that’s because the Customer table has relationships to those tables and the underlying page API exposes them.

Lets import the data and have a closer look:

And let’s now expand those last columns:

How cool is that!

Let’s try this with a document, like for example the salesOrders:

This means you can import almost all master data and related information, documents (posted and non posted), ledgers, …

Here’s a list:

• accounts
• agedAccountsPayables
• agedAccountsReceivables
• applyVendorEntries
• attachments
• balanceSheets
• bankAccounts
• cashFlowStatements
• companyInformation
• contacts
• contactsInformation
• countriesRegions
• currencies
• customerFinancialDetails
• customerPaymentJournals
• customerPayments
• customerReturnReasons
• customers
• customerSales
• defaultDimensions
• dimensions
• dimensionSetLines
• dimensionValues
• employees
• entityDefinitions
• generalLedgerEntries
• generalProductPostingGroups
• incomeStatements
• inventoryPostingGroups
• itemCategories
• itemLedgerEntries
• items
• itemVariants
• journalLines
• journals
• locations
• opportunities
• paymentMethods
• paymentTerms
• pdfDocument
• pictures
• projects
• purchaseInvoiceLines
• purchaseInvoices
• purchaseOrderLines
• purchaseOrders
• purchaseReceiptLines
• purchaseReceipts
• retainedEarningsStatements
• salesCreditMemoLines
• salesCreditMemos
• salesInvoiceLines
• salesInvoices
• salesOrderLines
• salesOrders
• salesQuoteLines
• salesQuotes
• salesShipmentLines
• salesShipments
• shipmentMethods
• subscriptions
• taxAreas
• taxGroups
• timeRegistrationEntries
• trialBalances
• unitsOfMeasure
• vendorPaymentJournals
• vendorPayments
• vendorPurchases
• vendors

and a lot of those have related tables…

This is much better compared to the legacy web services, right?

And you can also create similar APIs, by developing Page or Query API objects. I’ll dive into how that works in a later post.

Here’s already some information:

• API Query Type – Business Central | Microsoft Docs
• API Page Type – Business Central | Microsoft Docs
• Developing a custom API – Business Central | Microsoft Docs