Make Power Query use filter on expanded Entity on Odata API

Copper Contributor

Hi,


I'm using Power Query to import data of my OData API into Excel and Power BI.


Filtering works as expected for base query, for example, querying an entity set called "Address" and filter following a parameter called "city" work very well. I see in the log of my API that a url with the following argument : "http://myservice.com/odata/v4/Address?$filter=city = 'Paris'" is generated.


But, when I'm using an expanded table and I'm using a filter on a field of the expanded table, it's not working as expected. For example, I expand the entity set "Country" of the entity set "Address" and then I filter the country by name the url generated is "http://myservice.com/odata/v4/Address?$expand=Country" there's no filter all seems to be done thought Excel. I expected something like this "http://myservice.com/odata/v4/Address?$expand=Country($filter=name eq 'France')".


I need this, because, there's a huge amont of row in my expanded entity set, and filtering is mandatory for user in order to get data. If anybody could help me adapt my API or this a parameter to enable filtering on expanded table, I'll gratefull.

 

1 Reply

@jbarotin 

 

I am facing the same issue with an API I am building. I build my query in PowerBI with some filters in the expand, but I cannot get access to them.

 

Did you manage to fix this your API and get the filters from the Expand? Do you mind sharing it fi you got PowerBI to send you the filters of the expand entity.

 

Cheers