While exposing Web Services in Business Central is a simple and effective way for end users to pull data into Power BI, it is not always the most efficient approach. A standard web service will bring in all available fields for a given table, often without the ability to filter at the source. This can lead to unnecessary data volume, slower refresh times, and more work in Power BI to shape the dataset.

Creating a custom API in AL offers more control. You can define exactly which fields to expose, apply filters at the query level, and optimize the dataset for reporting before it ever reaches Power BI. This approach is especially useful when you want clean, ready-to-use data that does not require heavy transformations in Power BI.

	Web Service	Custom API
Setup Speed	Quick to Publish from BC without Coding Knowledge	Requires AL Coding in VS Code
Control over Fields	All Fields are Exposed by default	You choose exactly what fields
Filtering	Limited Filtering Capabilities (unless using OData String (Different Connector))	Filters can be applied in the API Query Itself
Performance	May pull more data than needed	Optimized for smaller, targeted datasets
Maintenance	Minimal Upkeep	Requires AL Development Knowledge

Pros of Creating a Custom API in AL

• Full control over which fields are exposed
• Ability to apply filters at the source for cleaner datasets
• Better performance with smaller, targeted data loads
• Data is shaped for reporting before it reaches Power BI

Cons of Creating a Custom API in AL

• Requires learning AL and using Visual Studio Code
• Development and publishing must be scheduled to avoid disrupting production (not ideal for mid-day changes)
• Longer setup time compared to publishing a web service

Requirements

• Visual Studio Code Downloaded
• Tenant ID (Instructions Below)
• Business Central License

Optional But Best Practice for Tracking & Version Control

• Github Desktop
• Github SCM

*Post coming soon on linking Power BI to GitHub in correlation with custom APIs. Creating a neat, fully tracked solution package with version control!*

Downloading VS Code: Notes

Use the link above, when installing use the following settings:

Github SCM Settings to Change:

Install Extensions in VS Code (Square Boxes on Left Nav Bar)

AL Language Ext & AZ AL Dev Tools

Next, you will want to follow the steps outlined in this blog from Marcel at Aardvark Labs. This Blog post will walk you though the process of Setting Up Your Business Central AL Development Environment. He also has a great VS Code Template to use to format your projects, which you can find in the blog post or download directly from HERE. Below are a few notes that I have taken as I went though the process

— Read Marcels Blog Here — This is Step 1 —

Here is my app.JSON and Launch.JSON once I completed the steps outlined in his Blog.

Now that we are setup in Visual Studio Code we can begin actually constructing our API to use in Power BI.

Query vs Page Type

First, decide what TYPE of API you would like to create. Here is some information on the two most applicable types to reporting using PBI.

When you are building an API in Business Central for reporting, especially if you want to connect it to Power BI, you usually choose between a Page API or a Query API.

A Page API is based on a page object in AL. It works well when you want to share data exactly as it appears in Business Central. They are quick to create and follow the standard OData structure. If your data is simple, a Page API is often the fastest way to get it out. The problem is that if you need data from multiple tables or want something more complex, it can be hard to manage.

A Query API is built for more advanced reporting. Queries let you join multiple tables, create nested relationships, and shape the data before sending it out (doing transformations on the BC side vs the Power BI/Fabric Side). This means you can combine sales orders, customer information, line items, and dimensions in one dataset. You also reduce the need for extra transformations in Power BI because the data is already clean and ready to use.

For simple flat data, a Page API works fine. For reporting that needs multiple joins or a more complex structure, a Query API will make your life easier.

Creating the API

In this example, once we’ve walked though the steps in Marcels blog here are the steps to creating your API for use in Power BI. In this example, I am going to use Sales Orders.

1. First, right-click on 12-Query. Now, if you’re feeling brave and want to code everything from scratch, go for it.. but if you’re anything like me (a coding newbie who thinks “syntax error” is a fancy coffee order), the easiest and friendliest way is to use the New AL File Wizard from the AZ AL Dev Tools. Trust me, it’s like training wheels for your code!

2. Here are my settings for the AL Wizard. One thing to note is that I added a prefix to my object (nap) — this helps distinguish my code and makes future maintenance easier. I changed the object type to API and created a group called Sales. Lastly, I gave the object both a singular and plural name using camelCase — for example, salesInvoice vs. salesInvoices.

Next, you’ll want to select the fields that are most applicable for your report. I recommend NOT sending everything. Only pick the fields you actually plan to use. This helps reduce model bloat and also prevents potential errors down the road if Microsoft decides to deprecate a field.

When you create an API query in Business Central, you could stop right after exposing and publishing just the header data by pressing F5. That’s totally fine if all you need is the basic information.

But if you want to build something more complex, you can add a data item to the bottom of your query (See Below). This lets you start joining related tables together and do some of the data transformation and merging right on the server side. That way, you don’t have to do all that heavy lifting later inside Power Query or Power BI.

It’s a great way to keep your reports faster and cleaner by handling complex data structures before they even leave Business Central.

Adding Data Items & Columns

Here, I added a DataItem to our Sales Header Query. Basically, I merged the Sales Order Lines onto the query by linking them through Document No (No). I filtered the lines to only include those with Type = Item, then selected just the Amount and Item No columns to show. From here you could continue to add additional DataItems or Columns contained within your DataItem.

One important thing to remember: the name you give your DataItem (shown in green) is the name that will appear later in Power BI.

Once your Query is ready, hit F5 to publish or push your code to Business Central.

Reminder: Publishing an extension will kick active users out, so it’s best to do this after hours or make sure you’re working in a private sandbox environment.

Reminder #2: F5 Publication only works when deploying to a SANDBOX! If you would like to move to production, update your launch.JSON (Environment Name & Type), and then copy the App.JSON file and search for Extension Management in Business Central. From here, you will install and your Extension will be live in Production (Post incoming on this!)

Using your Query/API in Power BI‘

1. Open Power BI Desktop or create a dataflow in Power BI Service.
2. Search for the Business Central Connector.
3. Sign in using your credentials.
4. Find your environment and expand the Advanced APIs.
5. Locate your API and select it.
6. Click Load or Transform.

Note that tables are structured differently from pages if you’re using web services. You might need to apply different filters when working at the table level. For example, in Sales Orders, pulling the header through web services only shows open and released orders. But when you create the API, you’ll see Quotes, Orders, and Invoices. The sales header includes everything that is unposted sales.

And that’s pretty much it. That’s how you create a custom API in VS Code, connect it to Business Central, and then use it in your Power BI reports. Once you have the API set up, it makes pulling exactly the data you need super easy and much cleaner than using default web services. Plus, having that custom connection means your reports stay up to date and accurate with less hassle. It’s a great way to streamline your workflow and get more control over your data.

A special thanks to Marcel at AardvarkLabs for all his awesome contributions to the community.