Search code examples
powerbimicrosoft-dynamicsdynamics-business-centralpower-platform

I can't read specific tables of Dynamics BC with Power BI

I am having troubles sincronising certain tables of our ERP (Dynamics Business Central) with Power BI. The steps that I am doing are explained below:

  1. Get Data
  2. Search Dynamics 365 Business central
  3. Search for the relevant tables

This is when Power BI doesn´t let me preview the information within the table called 'salesCreditMemoLines' (see image), contrary to other tables that I can see without troubles (enter image description here)

I appreciate your help in this issue.

Solution

  • This is expected error. Document lines collections in Business Central API require the respective document ID to be present in the request, otherwise it fails.

    This is the piece of code from the API page that throws this error.

        IdFilter := GetFilter(SystemId);
    DocumentIdFilter := GetFilter("Document Id");
    if (IdFilter = '') and (DocumentIdFilter = '') then
        Error(IDOrDocumentIdShouldBeSpecifiedForLinesErr);

    There are two ways to send the document ID. My examples below are querying sales orders, but the same applies to all document collections. First is request the lines along with the document header using $expand syntax:

    https://api.businesscentral.dynamics.com/v2.0/{{tenantid}}/{{environmentname}}/api/v2.0/companies({companyId})/salesOrders(orderId)$expand=salesOrderLines

    Another option is to query the document lines adding the $filter parameter:

    https://api.businesscentral.dynamics.com/v2.0/{{tenantid}}/{{environmentname}}/api/v2.0/companies(companyId)/salesOrderLines?$filter=documentId eq salesOrderId

    Filters can include ranges, so this way it's possible to request a collection of lines from multiple documents. https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/use-filter-expressions-in-odata-uris

    Neither of these methods is going to work in Power BI data source selection, though. An alternative way is to use an entity salesDocumentLines under the Web Services (legacy) option. Yes, it shows as legacy, but so far Microsoft has not announced any plans to remove the OData web services.

    https://learn.microsoft.com/en-us/dynamics365-release-plan/2021wave1/smb/dynamics365-business-central/enable-power-bi-connector-work-business-central-apis-instead-web-services-only