Table of Contents
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.
Using the parameters I could set:
- Which cases are returned.
- The fields from the case entity that are returned.
- Extract fields from related entities.
- The number of cases to return (useful for debugging / development).
- The start date of the period of interest.
- 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.
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.
Now all I need to do when I need to create a new report is:
- Copy the base report file.
- Update the single ODataFeed parameter.
- Refresh the dataset.
- 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.
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.