In the past, I have written about connecting Power BI to a Custom API data source. Now I’d like to talk about connecting Power BI to a custom D365FO service operations endpoint. In this example, I am going to use the Fastpath Audit Trail service operation endpoints we use for our reporting. This endpoint accepts a POST request be made against it and returns the data for the report requested from the body of the request.

Create Azure App Registration

The first thing we have to do is to determine how we want to authenticate to our endpoint. Some endpoints are anonymous and don’t require any authentication but most will require some sort of authentication. In our example we use Azure AD as our authentication source which requires us to set up an Azure app registration. This setup is slightly outside the scope of this blog post but here is some documentation around this process.

Quickstart: Register an application with Microsoft Identity Platform

Microsoft Identity Platform and OAuth 2.0 Authorization Code Flow

Power Query Code in Power BI Advanced Editor

The next thing to do is to launch Power BI and go to Get Data -> Blank Query.

This will open the Power Query Editor, right-click on your query and go to Advanced Editor, the dialog that appears will allow you to write a custom query to be executed. The language used by Power BI when creating a custom query is Power Query.

Because we want to connect to a D365FO instance, the Power Query code from the previous post needs to be changed slightly.

• D365URL – points to the base URL of your D365FO instance
• Source – is the final URL with the service operation group, service, and actual endpoint to be used
• AuthBody – the client id and secret parameters come from the Azure web app created previously, the resource parameter will be the D365URL
• Body – this is the body of the post request to be sent
• Report – will include the raw data from the endpoint

Transforming Report Data

If we execute this query, we can see that the data is successfully pulled but is not in a format that is able to be consumed by Power BI just yet. We need to actually break apart the Record object to show the individual columns of the data we are pulled back. The first step is to do a Convert -> To Table

You then have the ability to tell Power BI how this data is delimited. In our case, we didn’t have to modify the these options.

Now that we have successfully converted the data to a table, we can click on this button at the top of the column which expands the Record object to show each column of the record.

Once clicked you can see the columns that Power BI found in your data, in our case we selected all columns.

Results in Power BI

Fastpath Audit Trail tracks user changes made in the ERP, so we can now break things down to show changes made by table and changes made by user.