NCR with PowerBI Desktop

This tutorial is not intended to teach PowerBI. This is a guide intended for use by those who have some basic understanding of how to use PowerBI to present data.

Pre-Filtering Data

You can request pre-filtered data on the request, so you do not have to download all records in the data table. The following examples request only closed NCRs that were closed less than 365 days ago.

There are two different connectors in PowerBI you can use to connect to Business Central data. The Business Central connector and the OData Feed connector. If you are unsure which one to use, use the Business Central connector.

Business Central connector

Study the following example, update your query to match the below. Ensure the Environment (Orange) is set to your proper environment (leave as it was). Ensure the Company (Red) is set to the proper company (leave as it was) and add or change the text highlighted in green (and don’t forget the comma at the end of the seventh line). When done, click the ‘Close and Apply‘ button.

let
    Source = Dynamics365BusinessCentral.ApiContentsWithOptions(null, null, null, [UseReadOnlyReplica = true]),
    PRODUCTION = Source{[Name="PRODUCTION"]}[Data],
    #"My Company" = PRODUCTION{[Name="My Company"]}[Data],
    Advanced = #"My Company"{[Name="Advanced"]}[Data],
    #"itclad/ncr/v1.0" = Advanced{[Name="itclad/ncr/v1.0"]}[Data],
    ncrAging_table = #"itclad/ncr/v1.0"{[Name="ncrAging",Signature="table"]}[Data],
    filteredRows = Table.SelectRows(ncrAging_table, each [status] = "Closed"),
    filteredRows2 = Table.SelectRows(filteredRows, each [origClosedAge] < 366)
in
    filteredRows2

Right-click on the Data Source named ncrAging and rename it to ncrAgingClosed. This will help better identify it if you create another data source to retrieve NCRs that are not Closed.

OData connector

To use this option, you must first create a new Web Service in Business Central, such as this:

Edit the query in the Advanced Editor (Power Query Editor) as demonstrated in the Business Central connector shown earlier, but with the following information. Add the text highlighted in green, and leave the data highlighted in red from your original query. Then click the “Close and Apply” button.

let
    TodayMinus12Months = Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-12),
    TodayMinus12Months2DT = Text.From(Date.Year(TodayMinus12Months)) & "-" & Text.PadStart(Text.From(Date.Month(TodayMinus12Months)),2,"0") & "-" & Text.PadStart(Text.From(Date.Day(TodayMinus12Months)),2,"0") & "T00:00:00Z",
    Source = OData.Feed("https://api.businesscentral.dynamics.com/v2.0/86f6f5ff-a5a1-49f0-86d2-30234997xxxx/Production/ODataV4/Company('My%20Company')/ncrAging/?$filter=origClosedDate gt " & TodayMinus12Months2DT  & " and status eq 'Closed'", null, [Implementation="2.0"])
in
    Source