Search code examples
excelodatapowerquery

Use OData feed with parameters in excel power query

I am working with power query in excel and need to get some data from an OData feed. Everything works great for the feed actions that do not require parameters, but how can i use an OData feed and pass a parameter to it ?

Example :

http://url/odata/employees('00090')

The above throws an error in power query but works fine from a web browser

Thanks

Solution

  • Pasting http://url/odata/employees('00090') into Power Query should probably work if it works in the browser. If you could send-a-frown from the error, we might be able to fix a bug? :)

    The more canonical way to pass parameters to OData is trim your URL to the OData Service Document, probably http://url/odata and paste that into Power Query.

    Then you can right click on a cell in the column that's supposed to be 90000, and add a filter for Equals. (If none of the first rows are 90000, filter on e.g. 3 then edit the formula from = 3 to = 90000)

    Your formula would looks something like:

    let
    Source = OData.Feed("http://url/odata"),
    #"Filtered Rows" = Table.SelectRows(Source, each [ID] = 90000)
in
    #"Filtered Rows"