Help needed, including '$' prefix in OData v2 parameters with Power Query

[Gabry]

Hello everyone,

I'm trying to connect to a SAP OData v2 service using Power Query in Power BI. I'm accessing an endpoint that requires query parameters with the '$' prefix, as mandated by OData v2.

Here’s what I tried:

baseURL = "<SAP OData service URL>",
   
    queryOptions = [
        #"$filter" = "CreationDate ge datetime'" & RangeStartString & "'",
        #"$expand" = "to_....",
        #"$select" = "M...,date,to_..../M....,...",
        #"$top" = "10"
    ]

But I got the following error:

Expression.Error: OData.Feed custom query options cannot start with '$'.

If I omit the $ prefix and use this instead:

queryOptions = [

        filter = "CreationDate ge datetime'" & RangeStartString & "'",
        expand = "to_....",
        select = "....."

…the parameters are ignored entirely.

So, how can I include the $ prefix in the query parameters? Is there a workaround or a different approach to make this work? Anyone have any suggestions?

Thanks in advance! 😊

[v-cgao-msft]

Hi @Gabry ,

Please refer to Chris Webb's blog:

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

Query options overview - OData | Microsoft Learn

Hope these help.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Gabry]

Hello @v-cgao-msft ,

Thank you for your response. I came across Chris’s article yesterday and gave that approach a try, but unfortunately, it didn’t work for me either.

It seems that parameters containing "@" are not being passed correctly.

This is the code

let
     baseURL = "http://................?$top=@ttttt",
    
  
    Source = OData.Feed(
        baseURL,
        null,
        [
            Implementation = "2.0",
            Query = [              
                #"@ttttt" = "10"
            ]
        ]
    )
   
in
Source

then i get this error

Can you really pass parameters with @ inside brackets?

[v-cgao-msft]

Hi @Gabry ,

Yes, I tested it using the source in the blog.

let
  Source = OData.Feed(
    "https://services.odata.org/TripPinRESTierService/People?$filter=FirstName eq @fn and LastName eq @ln", 
    null, 
    [
      Implementation = "2.0",
      Query = [#"@fn" = "'Marshall'", #"@ln" = "'Garay'"]
    ]
  )
in
  Source

I seem to be returning a similar error here after removing "‘", try adding "‘".

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Gabry]

Thank you @v-cgao-msft  for following up!

I’ve tried both with and without the single quotes, but nothing changes — I still get the same error.

I also tested using the URL https://services.odata.org/, and it works fine with this one.

However, it doesn’t work with the SAP OData service. Do you think there’s any other workaround?

It’s frustrating that I have to rely on the web call. With odata would be much better, can't understand why I can't pass the parameters

[PhilipTreacy]

@Gabry 

Try Uri.BuildQueryString to assemble the query parameters

Uri.BuildQueryString - PowerQuery M | Microsoft Learn

Regards

Phil

[Gabry]

Hello @PhilipTreacy ,

thanks for the suggestion. I tried but then when published to the service can't be refreshed because of dynamic data source error.
Code was like this:

    baseURL = "http:......",
    
    
    queryParameters = [
        #"$filter" = "C....",
        #"$expand" = "to_M.....",
        #"$select" ="M......e",
        #"$top" = "10"
    ],
    
    queryString = Uri.BuildQueryString(queryParameters),
    
    fullURL = baseURL & "?" & queryString,
    
    Source = OData.Feed(fullURL, null),