Using Power BI to Report Bookings Events

During COVID times we are getting more and more customers using Bookings. The service, which is part of Microsoft 365, now has the ability to use Microsoft Teams to offer virtual sessions.

Annotation 2020-05-22 165853

Possibilities are endless. Banks that allow customers to interact virtually with account managers or investments experts, hospitals and clinics that offer virtual consultation (tele medicine), remote psychology sessions in challenging times, are just a few scenarios where customers can leverage Bookings.

As soon as Bookings get setup and people start using it to create appointments and have virtual sessions the work refocus to how report.

How about a way to connect Power BI to bookings and extract appointment information? That is possible.

When a new business is created on Bookings, a new user calendar is created in Exchange Online and we can connect Power BI to extract that information and build reports.

In the sample below I created two businesses in Bookings: TestHospital01 and TestHospital02.

Annotation 2020-05-20 161608

As you can see in the Booking Page status, each Hospital has a correspondent user created on Exchange Online/Office 365 and the user calendar is used to manage the appointments for each one of the hospitals.

Now that we have a correspondence between each business/hospital to a Exchange Online calendar we can leverage Power Platform to do a few interesting things. We can use Power Automate to fire specific actions based on events related to schedule/cancel appointments and we can also user Power BI to report on top of information extracted from calendars.

Focusing on Power BI, we can connect to calendars created by Bookings and report. There are two ways to do that: using Microsoft GRAPH and Power BI ODATA connector or using the native Exchange Online connector. The following steps show how to acquire data using the native ExO connector.

1. Open Power BI Desktop and click Get Data, search for the Exchange Online connector and click Connect.

Annotation 2020-05-22 174210

2. In Mailbox Address type the SMTP address of the user associated to the first business you created on Bookings. If you don’t know the SMTP address, open Bookings, click on Booking Page and copy the address from the Booking Page Status. Image below show the SMTP address in the Booking Page Status and the Power BI Mailbox Address text box.

UntitledAnnotation 2020-05-22 175233

3. If prompted to authenticate, please specify the credentials of the user that created the business in Bookings.

4. Select Calendar and click Transform Data.

Annotation 2020-05-22 175348

5. If you have more businesses to include on the report, click New Source and repeat the process I just described specifying the SMTP addresses for each business. In my case I’m adding two businesses. Test Hospital 01 and Test Hospital 02.

6. Now we need a way to consolidate all the information in a single table so we can create better reports. You can do that using the Append Queries command in Power Query. Just Click Append Queries and select Append Queries as New.

Annotation 2020-05-22 175641

7. Select the tables you are going to consolidate and click OK. In my case I’m creating a third table appending data from two other tables that have data from Test Hospital 01 and Test Hospital 02.

Annotation 2020-05-22 175751

8. The append command will create the Append1 table. In my case I decided to include an additional column on the resultant table to indicate the source (which hospital). To achieve that I used the Advanced Editor in Power Query to edit the M formula used to create the table. The code I used is below as a reference.

Annotation 2020-05-22 180200

let
Source = Table.Combine({Table.AddColumn(TestHospital01,”Source”, each “Hospital 01″)
, Table.AddColumn(TestHospital02,”Source”, each “Hospital 02”)})
in
Source

9. Now we have Append1 table with the consolidated data and we’re ready to clock  Close & Apply and start building reports.

Good luck.

-Cristiano

Disclaimer – The information contained in this blog post doesn’t represent the official Microsoft guidance or best practices. It is just the view of the author on current alternatives, implementations and workarounds for common issues and/or business needs. Please refer to official Microsoft documentation and evaluate carefully any steps, code or procedures documented herein. The author doesn’t offer any warranty. Use this information at your own risk.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s