Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
New OData parser (Implementation="2.0") ignores the filter restrictions (for fields with whith space in it) given in $Metadata
The new OData feed implementation in PowerBI (and unfortunately Excel in production, after last update) doesn’t respect the Non-Filterable property information and sends all of the filters directly to the server.
It happens for fileds with "space" in the field name.
The old implementation works good.
Example:
I have a OData feed named "Test" and I want to filter it in PowerBI by the field "Auto Number".
The field is described by the odata feed server as NonFiltrable (in Metadata).
The M statement looks like this:
“let
S1 = OData.Feed("http:\\test.test.net",null,null),
S2 = S1{[Name="test",Signature="table"]}[Data],
S3 = Table.SelectRows(S2, each ([Auto Number] = "123"))
in
S3”
The Statement sent to server is: http://test.test.net/tables/Test?$top=1000
and everything is ok….
“let
S1 = OData.Feed("http:\\test.test.net",null,[Implementation="2.0"]),
S2 = S1{[Name="test",Signature="table"]}[Data],
S3 = Table.SelectRows(S2, each ([Auto Number] = "123"))
in
S3”
The statement sent to server is: http://test.test.net/tables/Test?$filter=Auto Number eq '123'&$top=1000
And this is not good - property AutoNumber is described as "Non Filterable Property" but PowerBI sends the filter to the server.
Metadata in both cases looks like this:
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
…….
<Schema Namespace="Default" xmlns="http://docs.oasis-open.org/odata/ns/edm">
<EntityContainer Name="Container">
<EntitySet Name="Test" EntityType="TestNamespace.Test">
<Annotation Term="Org.OData.Capabilities.V1.FilterRestrictions">
<Record>
<PropertyValue Property="Filterable" Bool="true" />
<PropertyValue Property="RequiresFilter" Bool="true" />
<PropertyValue Property="RequiredProperties"><Collection /></PropertyValue>
<PropertyValue Property="NonFilterableProperties">
<Collection>
<PropertyPath>Auto Number</PropertyPath>
……..
As You see the property “Auto Number” is described as a Non-Filterable property and it shouldn’t be sent to the server
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.