OData.Feed And The Dynamic Data Sources Error In Power BI

I’ve blogged about the “dynamic data sources” error and the Web.Contents function several times (most recently here), main post here, but never about the fact that you can encounter the same error when working with OData data sources and the OData.Feed function. More importantly, while it seems like the solution to the problem is the same for both functions this is not the case! In fact, the solution may be simpler than you might think.

Let’s take a simple example. Say you have a table with three first names in and you want to use those names to filter the People enity in the TripPin OData sample service. Knowing a bit about how OData works, you might come up with some M code like this:

let
  Source = #table(
    type table [FirstName = text],
    {{"Scott"}, {"Angel"}, {"Ursula"}}
  ),
  #"Added Custom" = Table.AddColumn(
    Source,
    "Person",
    each OData.Feed(
      "https://services.odata.org/TripPinRESTierService/People?$filter=FirstName eq '"
        & [FirstName]
        & "'",
      null,
      [Implementation = "2.0"]
    )
  ),
  #"Expanded Person"
    = Table.ExpandTableColumn(
    #"Added Custom",
    "Person",
    {
      "UserName",
      "FirstName",
      "LastName"
    },
    {
      "Person.UserName",
      "Person.FirstName",
      "Person.LastName"
    }
  )
in
  #"Expanded Person"

This code uses the $filter system query option to filter the People entity by the three names provided; it’s not the most efficient code but it works fine in Power BI Desktop. Here’s the output:

However if you publish this to the Power BI Service you’ll get the following error in the dataset Settings page:

You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh

Looking at the docs you’ll see that the OData.Feed function has a Query option like the Web.Contents function and assume that the same solution that works for Web.Contents will work for OData.Feed and it can… but it’s not straightforward and I’ll need to write another post to explain why. (Update: that post is here)

Instead, all you need to do is a simple Merge operation between the first names table and the People table to get the desired result:

let
  Source = #table(
    type table [FirstName = text],
    {{"Scott"}, {"Angel"}, {"Ursula"}}
  ),
  #"Merged Queries" = Table.NestedJoin(
    Source,
    {"FirstName"},
    People,
    {"FirstName"},
    "People",
    JoinKind.Inner
  ),
  #"Expanded People"
    = Table.ExpandTableColumn(
    #"Merged Queries",
    "People",
    {
      "UserName",
      "FirstName",
      "LastName"
    },
    {
      "People.UserName",
      "People.FirstName",
      "People.LastName"
    }
  )
in
  #"Expanded People"

One key differences between OData sources and other web services is that query folding is possible on OData sources. Sure enough, if you use Query Diagnostics to see the calls made to the OData API, you’ll see the following call is made:

https://services.odata.org/TripPinRESTierService/(S(gprer1xbnmwlxoeb50woookg))/People?$filter=FirstName eq 'Scott' or FirstName eq 'Angel' or FirstName eq 'Ursula'&$select=UserName%2CFirstName%2CLastName

The Power Query engine has folded the Merge and generated an efficient request using the $filter and $select system query options without you needing to build one yourself.

The dataset will now refresh successfully after you have published it to the Power BI Service. There’s one last important point to mention: you’ll need to make sure the data privacy settings, set in the Power BI Service, for your data sources allow query folding to take place. To learn more about this topic you can watch the video here but TLDR if you’re OK with sending data from one data source to another then you can set all data sources to the “Public” privacy level you should be ok.