Filtering NAV's OData with Power BI

From time to time we get questions on how to filter NAV data when working with Power BI.

As an example let’s say we want to create a report on Item Ledger Entries in order to allow analyzing item sales and inventory value. This sort of entry table can become quite big in larger NAV installation, often exceeding 5 or 10 million records.

While Power BI’s in memory-engine is able to handle big transaction tables without problems mainly due to data compression, there are good reasons to limit the number of transactions to import into Power BI. One reason is to save time when (re-)loading data into Power BI.

To get my transactions, I am using a pretty ordinary query object:

Let's first import it into Power BI without any filtering:

I will get about 25k rows from my CRONUS Extended database.

The free debugging tool Fiddler helps to see what happened in the background. Since I had no Odata page size limit on my NAV Service instance, all the rows came in one batch. Fiddler shows this single response body with 5.8 MB:

Filtering in Power Query via User interface

The first (naive) option to filter data is in the query tab of Power BI:

Here I can select the column to filter and the values I want keep:

In this case I only care about Location Code Blue. Power BI only returns 6841 rows what looks ok:

In fiddler however I still get the same 5.8 MB Body. This means that Power BI is not able to perform a feature called Query Folding with Odata-feeds what means it currently does not know how to execute our filter-step prior to loading the data. When working with SQL-queries for example Power BI in most cases would be able to translate such a filter to a where-clause of the Select statement. This sort of optimization currently needs to be done manually when working with Odata.

And this is how it can be accomplished:

Odata-Filter-URL

Odata allows to define filters as part of the Odata Url. See https://msdn.microsoft.com/en-us/library/hh169248(v=nav.90).aspx for details.

In order to make use of this feature, I added the green part to my Odata-Feed:

= OData.Feed("https://navida-julian:7148/CRONUS_EXTENDED/OData/Company('CRONUS%20AG')/ItemLedgEntryENU?$filter=Location_Code eq 'BLAU'")

Now as expected Fiddler shows a much smaller response what indicates that NAV only returned those rows that met the filter requirements:

Instead of hard-coding the filter in the query, it would certainly make sense to use a parameter instead. (Unfortunately as per beginning of August parameters cannot be defined in PowerBI.com but need to be mainainted in Power BI Desktop.)

For the sake of completeness I want to mention another option to filter Odata-feeds. A staticfilter can be defined in the query object in NAV.

However this does not provide any flexibility to the reporting author.