cancel
Showing results for 
Search instead for 
Did you mean: 

New OData parser (Implementation="2.0") ignores the filter restrictions given in Metadata

 

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:

 

 

  1. The “old”-good working implementation 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….

 

 

 

  1. Now using the new “2.0” implementation:

“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

 

 

Status: Investigating
Comments
Microsoft

@rosponda57,

I have consulted this issue internally. Will post back once I get updates.

Regards,
Lydia

Impactful Individual
Status changed to: Investigating