Dynamics 365 Business Central (D365 BC) offers users the ability to query page data in Excel using Web Services. This allows users to have real-time Business Central data available in Excel sheets that can be queried and saved for future uses.
Note: this will not work in an incognito or InPrivate browser, the service will only work with a standard browser.
Excel Query Setup
In D365 BC, go to Web Services:
Of the three Object Types, Page is available for the user to control. Query needs development intervention and Codeunit means running a job queue task that can be scheduled overnight for things such as exchange rates, or running tables and tasks.
Under Page, find the corresponding Page to return data, for example, page 20 for General Ledger Entries. User’s can find the page number by going to the table, then clicking the question mark, click help and support, and then inspect pages and data to find the page number:
Go back to Web Services and publish the page (the highlighted area in the first screenshot in this blog). Then copy the OData V4 URL and go to Excel.
Under the Data tab in Excel, choose Get Data, then choose From Other Sources, and then click from OData Feed:
Then copy the URL into the field as shown below:
If prompted, Sign in using an Organizational account. If that receives an error message, try using the Windows sign in option and click Connect:
Once signed in (first time only), choose the load option:
When the user refreshes under Table Design, new entries entered in D365 BC are added to the list:
User’s should note that modifying data in Excel using DataLink query cannot be imported back to D365 BC. To do that users can use the Edit in Excel functionality on select tables and fields. Files with queries can be saved and opened later with the Refresh icon to obtain most the recent posted data from D365 BC.
If you have any questions about how to set up an Excel query in Dynamics 365 Business Central, please feel free to reach out to us.