Dynamics 365 Business Central- My Favorite Things #8- OData Access & Excel


Using OData with Excel

One of the many benefits of Dynamics 365 Business Central is the way you can easily extract data to connect via integration, ETL tools, API, OData, Power BI and more. One of my favorite parts of the web services feature is the ability to take a data query or page and create a report in Excel in a few simple steps. 

Here are the steps to do this: 

1. Inside Business Central, search for web services: 


2. Look for the page, query or code unit you want to create a report off of. In my case, I did a search for vendors. 

3. Scroll to the far right, on the line that you want to extract until you see OData URL. 

4. This will open the link in a webpage. Copy the full URL from the web browser. 

5. Open Excel. Create a blank workbook. 

6. Click on the Data tab at the top. Click on Get Data. Choose From Other Sources. Choose from OData Feed. 

7. Copy and paste the URL from step 6 in the URL box. Click OK. 

8. You will be prompted for authentication. Choose Organizational Account. Choose Sign in. 

9. You will be prompted to sign-in to your Business Central account using your Microsoft login. Click Connect when done. 

10. Data will upload to Excel. You can now use this data for pivot tables, slicers, analysis, etc. Enjoy!