Using PowerBI REST API to overcome frustrating oData URL filter limitation

Posted By Posted by: Peter Williams on May 28, 2019

The Problem

I had a need to create report that consumed the following data sources:

  • Project Online timesheet data
  • SharePoint Online list info
  • On-Premise SharePoint list info
  • Zendesk ticket data

I have a reasonable amount of experience using PowerBI to query and consolidate this information. My desire was to have a report that used essentially the queries in both PowerBI and Excel. I wanted to develop the report in both Excel and PowerBI because of the added capability to easily search for low level detail in Excel and the better visualizations in PowerBI. Since the Power Query engine is practically the same in both, I didn’t think this would be an issue.

For the most part it wasn’t. However, the big challenge and frustration was with the limitation inbuilt with auto refresh of datasets in PowerBI.com, along with there being no Zendesk “plug-in” for Excel.

The following “brick walls” were encountered:

  1. The PowerBI Zendesk plugin only works if you install the 32-bit version of Power BI desktop client. If you don’t use it, the authentication dialogue box doesn’t work.
  2. No Zendesk plug-in for Excel Power Query.
    I am therefore forced to use JSON calls in Power Query for Excel instead and decided to use the same code for PowerBI desktop client. That is, I didn’t use the Zendesk plugin in Power BI.
  3. I now discover that auto refresh of datasets in PowerBI.com does not support the JSON call methods employed.
    I now realize that I have no choice to use different queries in PowerBI and Excel. One that uses the Zendesk plugin (PowerBI) and one that uses JSON calls (Excel).
  4. I’m back to using a 32-bit version of Power BI Desktop Client because I’m using the Zendesk plugin. Guess what? It crashes all the time. 32-bit means less memory to play with and with PowerBI modelling, you want as much memory as possible.
    It seems that if I load too much data with the timesheet data queries, then PowerBI crashes. In order to publish the PowerBI report, I need to limit the timesheet queries to 2 timesheet periods.  However, I want to load almost 4 months’ worth, but more on that later.
  5. My Power Query employs “dynamic variables” to calculate the two most recent reporting periods and these are passed to the $filter query option in the odata query. For example, the yellow highlighted period names are dynamically created.
    https://epmp.sharepoint.com/sites/epm/_api/ProjectData/TimesheetPeriods?$filter = PeriodName eq ‘Wk 21 CY19’ or PeriodName eq ‘Wk 20 CY19’
    It is essential that I use the $filter query option, otherwise I will be forced to  download far more data than what is necessary.
    There is one client I know that has over a million rows of timesheet data; there is no way I want to do download all that unnecessarily.

And don’t forget, in my case, if I download too much data, the PowerBI 32-bit client just crashes. I am between a rock and a hard place!

So you can imagine my frustration, when I discover that dynamically creating URLs that are passed to the OData.Feed() Power Query function prevent PowerBI from being able to auto-refresh the dataset!!!

The Workaround

At this point I remember blog articles referring to the PowerBI API. I start to consider the possibility of using static parameters that contain “sample” Odata.Feed urls. Please refer to the below screenshot to get an idea of what I’m referring to.

What if I could have static OData.Feed urls that contain the $filter query option, so that only a small dataset is loaded, and then publish to PowerBI.com. Then have a PowerShell script that updates the PowerBI.com dataset once a week (immediately after a new timesheet period starts). The script could dynamically create the odata.Feed urls that will bring in the desired most recent 4 months’ worth of timesheet data.

After a few hours of research online I discover that this is indeed possible. The following articles were key references:

https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updateparameters

https://github.com/Azure-Samples/powerbi-powershell/blob/master/manageRefresh.ps1

https://docs.microsoft.com/en-au/power-bi/developer/register-app

Please find attached my working script.

In Summary

The PowerBI REST API saved the day. Without the ability to update parameters in a PowerBI dataset, via a background PowerShell script, I would never have been able to come up with a solution to my Zendesk/Project Online Timesheet report. And I would have been forced to only use my Excel version of the report.


Blog Posted In Blog Posted In: Blog, How to, Project Online, Reporting
Blog Posted In