Working with the ExpandCalendar Function in Power BI Desktop (Microsoft Dataverse / Dynamics 365)

Before we begin, I should highlight that most of the work in bringing this solution together came via my colleague Andrew Bennett, based on a high-level steer from yours truly. Thanks a lot, Andrew and well done for figuring all this out!

Several times for me recently, a requirement has arisen involving Dynamics 365 Project Service Automation / Project Operations and, specifically, of the need to report on the individual calendar bookings for a Bookable Resource. It seems like such a straightforward ask, right? Well, no, in fact - the table that stores this information behind the scenes, calendarrule, is completely locked down from an SDK standpoint, meaning it’s impossible to perform RetrieveMultiple requests against it to extract the information we want. Instead, Microsoft has made available a dedicated Web API function, called ExpandCalendar, that does precisely what its name implies. All we need to do is pass through the details of the Bookable Resources calendar, the date ranges we wish to interrogate further, and the relevant information from within the calendarrule table will generate. Working with this function for a single Bookable Resource doesn’t present too much of a challenge. But suppose we want to use Power BI instead and apply this function against many hundreds or thousands of rows. In that case, complications can arise because the Dataverse connector(s) available to us today do not allow us to work with these types of functions straightforwardly. The purpose of this post is to demonstrate an approach that will enable us to circumnavigate this limitation and work with the ExpandCalendar function in the context of multiple Bookable Resource rows. Get your belts tightened, as this could be a bumpy ride… 😅

To begin with, we need to jump into the Power Query Editor within our Power BI Desktop file to add a new parameter to our model. This will define the Web API URL of the Dataverse environment we’re connecting to:

Next, because for our scenario, we want to expand out the details of each Bookable Resources calendar as part of an existing table, we must then define a custom function that accepts the same inputs as the ExpandCalendar request. Create a new blank query and then copy & paste in the following snippet for the function:

let
    Source = (calendarid as text, start as date, end as date) => let
  relativePath = "calendars(" & calendarid &")/Microsoft.Dynamics.CRM.ExpandCalendar(Start=@start,End=@end)",
  queryStart = Record.AddField([], "@start", Date.ToText(start, "yyyy-MM-dd")),
  query = Record.AddField(queryStart, "@end", Date.ToText(end, "yyyy-MM-dd")),
    raw = Web.Contents(DataverseAPIAddress, [RelativePath=relativePath,Query=query]),
    json = Json.Document(raw),
    result = json[result]
in
    result
in
    Source

You’ll know you’ve added it correctly if you see something like this after hitting the Done option:

Now, bring the bookableresource table into your model using the standard Microsoft Dataverse connector. In this example, we’ll restrict the view down so that only the following columns are visible; but feel free to keep any additional columns you need:

bookableresourceid
calendarid
name
resourcetype

You can use the below snippet to speed things along here:

let
    Source = Cds.Entities(DataverseAPIAddress, null),
    entities = Source{[Group="entities"]}[Data],
    bookableresources = entities{[EntitySetName="bookableresources"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(bookableresources,{"bookableresourceid", "calendarid", "name", "resourcetype"})
in
    #"Removed Other Columns"

Finally, we now want to expand the bookableresource query and add a few manual steps to execute the ExpandCalendar function call. In this scenario, we need to return calendar bookings within a 12 month period (i.e. from the start to the end of the current year). So we can add on a few steps to our query to figure this out and then put together the correct dates that we want to pass through. Here’s the updated bookableresource query snippet to use:

let
    Source = Cds.Entities(DataverseAPIAddress, null),
    entities = Source{[Group="entities"]}[Data],
    bookableresources = entities{[EntitySetName="bookableresources"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(bookableresources,{"bookableresourceid", "calendarid", "name", "resourcetype"}),
    currentDateTime = DateTime.LocalNow(),
    currentDate = DateTime.Date(currentDateTime),
    startOfCurrentYear = Date.StartOfYear(currentDate),
    startOfLastYear = Date.AddYears(startOfCurrentYear, -1),
    endOfNextYear = Date.AddYears(startOfCurrentYear, 2),
    result = Table.AddColumn(#"Removed Other Columns", "Calendar", each Table.FromRecords(ExpandCalendarRequest([calendarid],startOfLastYear, endOfNextYear)))
in
    result

Note you may, at this stage, get a privacy level warning - in the dialog that appears, set this to the same as your other data sources, and then the data should load fine:

As a table object is returned for each row, we then need to define which columns we are interested in viewing. In this example, we want to see the start / end dates and determine the type of appointment we are looking at:

From there, you can then proceed to build out your report and then deploy it out to the online service, if needed.

I’m still a little baffled why the calendarrule table is locked down the way it is. Still, with the ExpandCalendar function, I’m thankful that we have a route to interrogating the platform further for what is a pretty vital information point that most organisations are keenly interested in. I hope this post helps you work with this function using Power BI and avoid a situation where you have to consider alternate, bespoke routes to meet a similar requirement.