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 response body. This means that Power BI is not able to perform a feature called Query Folding with Odata-feeds. Query folding means that Power BI combines multiple steps such as filtering, sorting or grouping into one comprehensive query to the data source. When working with SQL-queries for example Power BI in most cases would be able to translate a filter steps such as our previous 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 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 red part to the 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 maintained in Power BI Desktop.)
Note: Working with date fields seems to be slightly more difficult. With „normal“ OData-feeds it should be possible to add a filter such as ?$filter=year(Posting_Date) gt 2015
However Power BI will give a cryptic error:
DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (500) Internal Server Error. (An error occurred while processing this request.)
OData Version: 4, Error: The remote server returned an error: (415) Unsupported Media Type. (Unsupported media type requested.)
OData Version: 3, Error: The remote server returned an error: (500) Internal Server Error. (An error occurred while processing this request.)
Thanks to Fiddler the inner error can be seen what is more helpful: Client requests that contain „Year“ filter options are not supported by Microsoft Dynamics NAV OData web services.
Fortunately the following syntax is working without any issue:
$filter=Posting_Date gt datetime’2010-01-01T00:00:00.0000000Z'“)
Static filter in Query
For the sake of completeness, I want to mention another option to filter Odata-feeds. A static filter can be defined in the query object in NAV.
However this does not provide any flexibility to the reporting author.