Fixing the 'Unsupported Function' error when using OData.Feed in Power BI

Table of Contents

Challenge

Earlier this year I was developing a set of reports in Power BI. The reports are using data extracted from our Microsoft Dynamics CRM instance.

My aim was to develop a base report that I could use with different datasets. I also wanted to avoid having to edit the Power Query formula each time I wanted to use a new dataset.

To this end, I used a set of parameters that stored components of an OData (Open Data Protocol) query. You can see the list of parameters in the screen capture below.

An example of the parameters I wanted to use

An example of the parameters I wanted to use

Using the parameters I could set:

  1. Which cases are returned.
  2. The fields from the case entity that are returned.
  3. Extract fields from related entities.
  4. The number of cases to return (useful for debugging / development).
  5. The start date of the period of interest.
  6. The end date of the period of interest.

The combination of the parameters is a single URI. The URI was used with the OData.Feed function in a Power Query formula.

The formula worked well in Power BI Desktop. I could change the parameters and use the same visualisations with different datasets.

Uploading the report to the Power BI Service did not work. The Power Query formula failed, and I could not refresh the data. Attempts to do so resulted in the following error:

You can't schedule refresh for this dataset because the following data sources don't support refresh:

[ Data source names removed]

Query contains unsupported function. Function name: OData.Feed

The error message made absolutely no sense. Other reports were using the OData.Feed function. I needed to work out what was different between the reports that worked, and my reports that didn't.

Solution

The key realisation I had was this:

It wasn't that the OData.Feed function that isn't supported. It was the way that I was using the function that isn't supported.

The Power BI Service uses static analysis to match services to authentication credentials. By constructing the URI from a series of parameters the static analysis failed. The result was that the Power BI Service couldn't determine how to authenticate.

I needed to find a way to continue to use my report as a template, and still be able to update the OData URIs. I didn't want to have to edit the Power Query formula. I still wanted to be able to change datasets with as little change to the code as possible.

The key to the solution is that parameters are read-only values. This means that I can still use parameters, if I only use one for the entire OData feed URI. Below is a screen capture of the single parameter.

An example of the single parameter I now use

An example of the single parameter I now use

Now all I need to do when I need to create a new report is:

  1. Copy the base report file.
  2. Update the single ODataFeed parameter.
  3. Refresh the dataset.
  4. Publish the new report and schedule automatic refreshes.

The last remaining challenge was how to make it easier to construct the OData query. To solve this challenge I developed a small Microsoft Excel Workbook. I enter the same values I did in the parameters, and it generates the OData query URL for me.

A screen capture of the Excel workbook

A screen capture of the Excel workbook

Benefits

The benefits of this solution are that it is still easy to use the same base report with different datasets. Each time I need to use a different dataset I can use the base report and have only one parameter to update.

I also relearnt an important lesson. Sometimes an error message isn't telling you the full story and isn't as helpful as it may first appear.