Using OData.Feed And The Query Option To Avoid The Dynamic Data Sources Error In Power BI

In my last post I showed how, in many cases, you can avoid the “dynamic data sources” error with OData data sources by taking advantage of query folding. That’s not always possible though and in this post I’ll show you how you can use the Query option of the OData.Feed function to do so instead.

At first glance the Query option of OData.Feed looks very much like the Query option on Web.Contents which, of course, can also be used to avoid the dynamic data sources error (see here and here) and that’s true up to a point: you can use it to add query parameters to your OData URL. However the documentation is not particularly detailed and there is one thing that will confuse you when you try to use it: you can’t use it with OData system query options like $filter. For example, let’s say you wanted to query the People entity in the TripPin sample OData endpoint to get only the people whose first name was Scott. You can do this as follows:

let
  Source = OData.Feed(
    "https://services.odata.org/TripPinRESTierService/People?$filter=FirstName eq 'Scott'",
    null,
    [Implementation = "2.0"]
  )
in
  Source

Since $filter is a url query parameter, you might then try the following code:

let
  Source = OData.Feed(
    "https://services.odata.org/TripPinRESTierService/People",
    null,
    [
      Implementation = "2.0",
      Query = [
        #"$filter"
          = "FirstName eq 'Scott'"
      ]
    ]
  )
in
  Source

Unfortunately, this returns the following error:

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

This is Power Query’s way of telling you you can’t use OData system query options with the OData.Feed Query option, since they always start with a $ sign. This is a deliberate design decision on the part of the Power Query team; I won’t go into the reasons why it is this way, it’s complicated!

However, you can use the Query option with OData custom query options like so:

let
  Source = OData.Feed(
    "https://services.odata.org/TripPinRESTierService/People",
    null,
    [
      Implementation = "2.0",
      Query = [#"debug-mode" = "true"]
    ]
  )
in
  Source

More importantly, you can use the Query option with OData parameter aliases. The original URL we were looking at:

https://services.odata.org/TripPinRESTierService/People?$filter=FirstName eq 'Scott'

…can be rewritten to use parameter aliases like so:

https://services.odata.org/TripPinRESTierService/People?$filter=FirstName eq @fn&@fn='Scott'

…and the rewritten version can be called using the Query option like so:

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

You can use parameter aliases to call OData unbound functions too. For example, you can call TripPin’s GetNearestAirport function like so:

let
  Source = OData.Feed(
    "https://services.odata.org/TripPinRESTierService/GetNearestAirport(lat = @latitude, lon = @longitude)", 
    null, 
    [
      Implementation = "2.0", 
      Query = [
        #"@latitude"  = "100", 
        #"@longitude" = "0"
      ]
    ]
  ), 
  #"Converted to Table" = Record.ToTable(
    Source
  )
in
  #"Converted to Table"

Finally, here’s an example of a query that starts with a table of latitudes and longitudes and calls the GetNearestAirport function for each row:

let
  Source = #table(
    type table [
      LocationLatitude = text,
      LocationLongitude = text
    ],
    {
      {"10", "10"},
      {"33", "-118"},
      {"100", "-100"}
    }
  ),
  #"Added Custom" = Table.AddColumn(
    Source,
    "FunctionCall",
    each OData.Feed(
      "https://services.odata.org/TripPinRESTierService/GetNearestAirport(lat = @latitude, lon = @longitude)",
      null,
      [
        Implementation = "2.0",
        Query = [
          #"@latitude" = [
            LocationLatitude
          ],
          #"@longitude" = [
            LocationLongitude
          ]
        ]
      ]
    )
  ),
  #"Expanded FunctionCall"
    = Table.ExpandRecordColumn(
    #"Added Custom",
    "FunctionCall",
    {"Name", "IcaoCode", "IataCode"},
    {
      "FunctionCall.Name",
      "FunctionCall.IcaoCode",
      "FunctionCall.IataCode"
    }
  )
in
  #"Expanded FunctionCall"

When you first publish a dataset with this query in to the Power BI Service you’ll see an internal server error message coming from the TripPin OData service. You can make the dataset refresh successfully and avoid this and the dynamic data sources error though: you need to check the Skip Test Connection box in the credentials dialog you can open from the Settings pane and set the data privacy level on the data source that is sending data to the OData function appropriately too: