How Do I: Create a simple API query and page for PowerBI?

In this post I will demonstrate how you can create simple APIs of type Query and Page to be used in Power BI. The idea is to have a look at an example where we create 2 very simple APIs: a Customers Query and a Customers Page.

In VSCode, create a new project using AL:Go! and setup your .json files. More information on how to do that is available here:

Next create a new file: Customers.Page.al, with the following code:

To create the page you can use a snippet: tpage – Page of type API. More information is available here: API Page Type – Business Central | Microsoft Docs

As an alternative I used the AZ AL Dev Tools/AL Code Outline extension for VSCode: AZ AL Dev Tools/AL Code Outline – Visual Studio Marketplace which has a wizard to create objects. Use the New Page wizard and make sure to set the PageType to API.

I used the following properties:

APIGroup = ‘powerBI’;
APIPublisher = ‘contoso’;
APIVersion = ‘v2.0’;
Caption = ‘customers’;
DelayedInsert = true;
EntityName = ‘pagcustomer’;
EntitySetName = ‘pagcustomers’;
PageType = API;
SourceTable = Customer;
DataAccessIntent = ReadOnly;
Editable = false;

Then I selected the Customer as the SourceTable and some fields like: No., Name, Country, City, Salesperson, as you can see in the above screenshot.

That’s it, the API Page is ready.

Next, create a new file: Customers.Query.al for the API Query.

To create the content of the API Query you can use the snippet: tquery – Query of type API or also the Query wizard in the AZ AL Dev Tools/AL Code Outline extension for VSCode.

In the query I used the same properties and fields as I did when creating the page:

APIGroup = ‘powerBI’;

APIPublisher = ‘contoso’;

APIVersion = ‘v2.0’;

EntityName = ‘qrycustomer’;

EntitySetName = ‘qrycustomers’;

QueryType = API;

DataAccessIntent = ReadOnly;

and here’s the code of the API Query:

That’s it, the API Query is ready.

More information is available here: API Query Type – Business Central | Microsoft Docs

Next you simply publish your app to your sandbox, and launch Power BI Desktop to consume the APIs.

I used my template, which you can find here: blog/MultiEnvironment at master · srenders/blog (github.com)

Then I filtered the _api query to only show the Advanced APIs, since that is where you can find the custom APIs you published to BC:

let
Source = Dynamics365BusinessCentral.ApiContents(null, null, null),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Name", "Environment"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Environment", type text}}),
#"Filtered Environment" = Table.SelectRows(#"Changed Type1", each [Environment] = Environment),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Environment",{"Environment", "Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Name", "Display_Name", "Data", "ItemKind", "ItemName", "IsLeaf"}, {"Name", "Display_Name", "Data.1", "ItemKind", "ItemName", "IsLeaf"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Data",{{"Name", "Company"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Company", type text}}),
#"Removed Other Columns1" = Table.SelectColumns(#"Changed Type",{"Environment", "Company", "Data.1"}),
#"Filtered <> MyCompany" = Table.SelectRows(#"Removed Other Columns1", each ([Company] <> "My Company")),
#"Expanded Data.1" = Table.ExpandTableColumn(#"Filtered <> MyCompany", "Data.1", {"Name", "Data", "DisplayName", "ItemKind", "ItemName", "IsLeaf"}, {"Name", "Data", "DisplayName", "ItemKind", "ItemName", "IsLeaf"}),
#"Removed Other Columns2" = Table.SelectColumns(#"Expanded Data.1",{"Environment", "Company", "DisplayName", "Data"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Other Columns2",{{"DisplayName", "Type"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Type", type text}}),
#"Expanded Data1" = Table.ExpandTableColumn(#"Changed Type2", "Data", {"Name", "Data", "Signature"}, {"Name", "Data.1", "Signature"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded Data1",{{"Name", "apiName"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns3",{{"apiName", type text}}),
#"Removed Other Columns3" = Table.SelectColumns(#"Changed Type3",{"Environment", "Company", "Type", "apiName", "Data.1"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns3", each ([Type] = "Advanced APIs") and ([apiName] = "contoso/powerBI/v2.0")),
#"Renamed Columns4" = Table.RenameColumns(#"Filtered Rows",{{"Data.1", "Data"}}),
#"Expanded Data2" = Table.ExpandTableColumn(#"Renamed Columns4", "Data", {"Name", "Data"}, {"Name", "Data.1"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Data2", each ([Name] = "pagcustomers" or [Name] = "qrycustomers")),
#"Renamed Columns5" = Table.RenameColumns(#"Filtered Rows1",{{"apiName", "apiInfo"}, {"Name", "apiName"}}),
#"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns5",{{"apiName", type text}}),
#"Renamed Columns6" = Table.RenameColumns(#"Changed Type4",{{"Data.1", "apiData"}})
in
#"Renamed Columns6"

Now you can reference the _apis query twice and filter it on the API Page and API Query Customers:

let
Source = _apis,
#"Filtered Rows" = Table.SelectRows(Source, each ([apiName] = "qrycustomers")),
#"Expanded apiData" = Table.ExpandTableColumn(#"Filtered Rows", "apiData", {"customerNo", "customerName", "countryRegionCode", "city", "salespersonCode"}, {"customerNo", "customerName", "countryRegionCode", "city", "salespersonCode"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded apiData",{{"customerNo", type text}, {"customerName", type text}, {"countryRegionCode", type text}, {"city", type text}, {"salespersonCode", type text}})
in
#"Changed Type"

let
Source = _apis,
#"Filtered Rows" = Table.SelectRows(Source, each ([apiName] = "pagcustomers")),
#"Expanded apiData" = Table.ExpandTableColumn(#"Filtered Rows", "apiData", {"customerNo", "customerName", "countryRegionCode", "city", "salespersonCode"}, {"customerNo", "customerName", "countryRegionCode", "city", "salespersonCode"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded apiData",{{"customerNo", type text}, {"customerName", type text}, {"countryRegionCode", type text}, {"city", type text}, {"salespersonCode", type text}})
in
#"Changed Type"

That’s it. Now Close & Apply and the data is loaded into your data model:

As you can see you have now imported Customers using an API of type Page and Query. There’s not mutch difference between the Page and Query in the design process, and the data in Power BI is the same.

By using the DataAccessIntent = ReadOnly, I implemented Using Read Scale-Out for Better Performance – Business Central | Microsoft Docs in both the API Query and API Page.

The query might be a tiny bit slower compared to the page, because the page data is cached on the server and the query is not. However I don’t think you will notice that, because in Power BI we typically refresh every X number of hours and by then the cache is probably gone 😉

The query I find a little easier to create and read in the AL code.

That’s how easy it is to create a simple API Page and Query.

Remember that there are standard APIs for Business Central master and transactional tables: Did you know that the Business Central connector in Power BI supports related tables when using APIs? – think about IT

So you would only need to create new APIs when the standard ones don’t contain the fields/tables you require.

In a next post I will go a little deeper and compare how you can create API Queries and Pages that combine/join data from multiple tables.

Feel free to download the example code and Power BI report here: