Make Power Query use filter on expanded Entity on Odata API

%3CLINGO-SUB%20id%3D%22lingo-sub-857311%22%20slang%3D%22en-US%22%3EMake%20Power%20Query%20use%20filter%20on%20expanded%20Entity%20on%20Odata%20API%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857311%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI'm%20using%20Power%20Query%20to%20import%20data%20of%20my%20OData%20API%20into%20Excel%20and%20Power%20BI.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EFiltering%20works%20as%20expected%20for%20base%20query%2C%20for%20example%2C%20querying%20an%20entity%20set%20called%20%22Address%22%20and%20filter%20following%20a%20parameter%20called%20%22city%22%20work%20very%20well.%20I%20see%20in%20the%20log%20of%20my%20API%20that%20a%20url%20with%20the%20following%20argument%20%3A%20%22%3CA%20href%3D%22http%3A%2F%2Fmyservice.com%2Fodata%2Fv4%2FAddress%3F%24filter%3Dcity%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fmyservice.com%2Fodata%2Fv4%2FAddress%3F%24filter%3Dcity%3C%2FA%3E%20%3D%20'Paris'%22%20is%20generated.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EBut%2C%20when%20I'm%20using%20an%20expanded%20table%20and%20I'm%20using%20a%20filter%20on%20a%20field%20of%20the%20expanded%20table%2C%20it's%20not%20working%20as%20expected.%20For%20example%2C%20I%20expand%20the%20entity%20set%20%22Country%22%20of%20the%20entity%20set%20%22Address%22%20and%20then%20I%20filter%20the%20country%20by%20name%20the%20url%20generated%20is%20%22%3CA%20href%3D%22http%3A%2F%2Fmyservice.com%2Fodata%2Fv4%2FAddress%3F%24expand%3DCountry%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fmyservice.com%2Fodata%2Fv4%2FAddress%3F%24expand%3DCountry%3C%2FA%3E%22%20there's%20no%20filter%20all%20seems%20to%20be%20done%20thought%20Excel.%20I%20expected%20something%20like%20this%20%22%3CA%20href%3D%22http%3A%2F%2Fmyservice.com%2Fodata%2Fv4%2FAddress%3F%24expand%3DCountry(%24filter%3Dname%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fmyservice.com%2Fodata%2Fv4%2FAddress%3F%24expand%3DCountry(%24filter%3Dname%3C%2FA%3E%20eq%20'France')%22.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20need%20this%2C%20because%2C%20there's%20a%20huge%20amont%20of%20row%20in%20my%20expanded%20entity%20set%2C%20and%20filtering%20is%20mandatory%20for%20user%20in%20order%20to%20get%20data.%20If%20anybody%20could%20help%20me%20adapt%20my%20API%20or%20this%20a%20parameter%20to%20enable%20filtering%20on%20expanded%20table%2C%20I'll%20gratefull.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-857311%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

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.

 

0 Replies