Forum Discussion

Gabry's avatar
Gabry
Super User
1 year ago

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

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! 😊

Need Help

7 Replies

    • Gabry's avatar
      Gabry
      Super User
      1 year ago

      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),
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Gabry
      Super User
      1 year ago

      Hello Anonymous ,

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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

  • metrica's avatar
    metrica
    Post Prodigy
    1 month ago

    Hi Gabry 

     

    OData.Feed does not allow system query options such as $filter or $select in its Query record. Building the full URL can work in Desktop, but Power BI Service may classify it as a dynamic data source and block scheduled refresh.

     

    If your filters can be configured in advance, you may want to evaluate Power BI Connector for SAP:

    https://www.sap.com/products/data-cloud/partners/metrica-software-inc-power-bi-connector-for-sap.html

    It supports SAP-side filtering through a stable OData endpoint. Compatibility with your SAP OData and incremental refresh scenario would need to be verified. A 30-day free trial is available, and we would be happy to arrange a demo. Support: https://metricasoftware.com/docs/sap/contact-support-sap/

     

    Cheers,
    Metrica Team