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.
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.
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.
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.
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.
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.
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.
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.
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