OData feed with Parameter Aliases not Working

[RodneydeBeer]

Hello.

I am following this blog that has been refered to in other threads on this forum.

Using OData.Feed and the Query option to avoid the dynamic data sources error in Power BI (crossjoin...

The parameter is however not being substitued into the Odata query string.

My Power Query:

and then the result:

Has anybody got advise please?

Many thanks

Rodney

[nedpbi]

hi @RodneydeBeer ,

I was able to resolve this finally and documenting here if this may help someone.

In my case I was passing a filter that I wanted to pass dynamically based on the RangeStart and RangeEnd parameters so that I could configure the incremental refresh from the OData feed.

I was trying something similar like you, something like the below:

This resulted in the token error that you mentioned above. I tried replacing the $filter with filter but this did not work on the OData side and data was not being filtered.

Finally I decided to retry the above with a simpler approach and defined the OData feed using the standard way and selected my table. Then I just filtered tha OData table using the parameters RangeStart and RangeEnd and this worked.

Looks like the OData driver uses query folding to send the right filters to the server.

The code that works looks like below:

I used fiddler to track the url sent to the server and power bi sent the following url from the above to the backend

http://xxx/xxxtable_name?$filter=LastUpdatedOn ge datetime'2024-10-01T00:00:00' and LastUpdatedOn le datetime'2024-10-31T00:00:00' and Status ne ' '&$select=xxxxcomma_separated_list_of_columns_from_the_table

In your case above you could use a parameter of type text named text_parameter to filter the CreatedByUser, something like the below:

#"Filtered Rows" = Table.SelectRows(#"OData table", each Text.Contains([CreatedByUser], text_parameter))

OData will use query folding to send this filter the right way to the server.

Hope this helps !

[v-stephen-msft]

Hi @RodneydeBeer ,

You can try the below points first that are often the cause of issues like the one you're experiencing.

The error (400) Bad Request with the invalid filter expression 'CreatedByUser eq@p' suggests that there is an issue with the syntax of the filter expression you are using in your API request.

To address this issue, please ensure the following:

1.Correct Filter Syntax: The filter expression should follow the Power BI API's expected syntax. A proper filter expression would look like Table/Field eq 'value'. For example, if you're filtering by a user who created a report, it should be something like:

CreatedByUser eq 'JohnDoe'
Replace 'JohnDoe' with the actual username you want to filter by.

2.Parameter Usage: If you are trying to use a parameter in your filter, ensure that you are passing the parameter value correctly. The @p notation is not recognized as a proper way to pass parameters in the filter expression for Power BI's exportToFile API. Parameters should be explicitly defined in the API call.

3.Check Field Names: Verify that CreatedByUser is the correct field name within the dataset you are trying to filter. If the field name is incorrect, the filter will not work.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[RodneydeBeer]

Hi Stephen. Thanks for replying.

The idea is to build the Odata query using dynamic values as described in the blog I mentioned. 

The way to use Parameter aliases is described here:

Query options overview - OData | Microsoft Learn

I am trying to figure why the paramter is not being substituted into the query, when I run it.

If I use hardcoded values in the query it works fine.

Rodney

[nedpbi]

@RodneydeBeer were you able to fix this ? 

I am trying to do something similar but I get the same error. The error goes away if instead of $filter in the url, i use filter without the $ sign. I think the parameters are passed but I don't see the data being filtered.

Thanks !

[RodneydeBeer]

Hi.

I could not. Was extremely frustrating for such a basic requirement. Not being able to build a dynamic querystring is, in my view, a major flaw in the product.

Eventually I hardcoded the querystring and we put a standard operating procedure in place for the support guys to MANUALLY change the hardcoding every month. Terrible, but it is what it is.

Cheers

Rodney

[nedpbi]

Thanks @RodneydeBeer for getting back !

Yes, frustrating. As I mentioned when i replace $filter with filter in the url, it does not give the error and seems like the parameter alias works and is passed to the backend, but the OData feed does not seem to filter this for some reason. I am not sure if this is because of the configuration on the OData feed or if the filter is not interpreted correctly as its missing the $ sign.

Hopefully they will fix this in the future.

Thanks again !