OData.Feed $filter ignored and all server side data downloaded

[DaveTheOx]

Execute the following M language in PBI Desktop (March 2018 release)

let

Headers = [],

ODataOptions = [ODataVersion = 4.0, MoreColumns = true],

Source = OData.Feed("https://c9loadtestsam.ingrnet.com/SampleService/Sppid/V2", Headers , ODataOptions),
Pipes_table = Source{[Name="Pipes",Signature="table"]}[Data],
#"Filtered Rows" = Table.SelectRows(Pipes_table, each [Pressure] = "9 psi")
in
#"Filtered Rows"

Results in the following requests sent to the SampleService OData API:

1. https://c9loadtestsam.ingrnet.com/SampleService/Sppid/V2    [this is correct]
2. https://c9loadtestsam.ingrnet.com/SampleService/Sppid/V2/$metadata [this is correct]
3. https://c9loadtestsam.ingrnet.com/SampleService/Sppid/V2/Pipes?$filter=Pressure%20eq%20%279%20psi%27... [this is correct]
4. https://c9loadtestsam.ingrnet.com/SampleService/Sppid/V2    [this is duplicate]
5. https://c9loadtestsam.ingrnet.com/SampleService/Sppid/V2/$metadata [this is duplicate]
6. https://c9loadtestsam.ingrnet.com/SampleService/Sppid/V2/Pipes?$top=1000 [this is not correct, no filter applied]
7. https://c9loadtestsam.ingrnet.com/SampleService/Sppid/V2/Pipes?%24top=1000&%24skip=25  [not correct, no filter applied]
8. https://c9loadtestsam.ingrnet.com/SampleService/Sppid/V2/Pipes?%24top=1000&%24skip=50 [not correct, no filter applied]
9. ...  [continue paged requests until all data downloaded

Steps 1-3 are correct with an OData $filter properly applied.   However, it seems PBI throws this result away and then reissues the requests and download all of the server data with no server-side filter applied.

[v-qiuyu-msft]

Hi @DaveTheOx,

I have sent a email to consult this issue internally. Will update here once I get any feedback. 

Best Regards,
Qiuyun Yu 

[DaveTheOx]

What is the status of this issue?   I just checked the latest Power BI Desktop Version: 2.61.5192.601 64-bit (August 2018) and still see the problem.   
I used this M lang:

let

    Source = SmartApiOData.Feed("https://samqa.ingrnet.com/sam/management/v1", null),

    Transactions_table = Source{[Name="Transactions",Signature="table"]}[Data],

    #"Filtered Rows1" = Table.SelectRows(Transactions_table, each [StartDateTime] > #datetimezone(2018, 8, 20, 15, 37, 0, 0, 0)),

    #"Counted Rows" = Table.RowCount(#"Filtered Rows1")

in

    #"Counted Rows"

Which resulted in this:

https://samqa.ingrnet.com/sam/management/v1/Transactions?$filter=StartDateTime%20gt%202018-08-20T15%...

which returned 7 rows, correctly.   However, Power BI then immediately issued this request:
https://samqa.ingrnet.com/sam/management/v1/Transactions?$top=1000

which pulled 1000 rows.   Power BI seems to be totally ignoring the filtered response and just issues a full request and performs client side filtering.

Thanks,

Dave

[DaveTheOx]

Hi @v-qiuyu-msft, has a CRI been filed for this issue?   We having significant performance problems due to the issuing of queries without proper $filters.

Thanks,
Dave

[DaveTheOx]

Hi @v-qiuyu-msft, I haven't heard any feedback on this issue.   We are seeing performance problems due to PowerBI issuing OData requests without $filter.   I can provide a test case for this issue if needed.

Thanks,
Dave