Overview

WaitWell's API can be integrated with Microsoft Power BI to enable comprehensive ticket data analysis and reporting. This integration allows you to:

Import all ticket data from WaitWell into Power BI for analysis
Create custom charts, dashboards, and reports
Handle large datasets through automated pagination (chunking)
Connect WaitWell data with other business intelligence tools

The integration uses a custom Power BI script that handles the WaitWell API's pagination system, as the API doesn't provide all data in a single operation (which could be millions of records).

Prerequisites

Before setting up the Power BI integration, ensure you have:

WaitWell Site ID: Your unique site identifier
WaitWell API Key: Generated from your staff account
Microsoft Power BI: Desktop or Pro version
API Access: Your API key will have access to whatever your staff account can access

Setup Requirements

Step 1: Obtain Your Site ID

To locate your Site ID:

Navigate to Settings > Customer Site/App > Customer Site Settings
Find your main client website URL, which will include a 3-6 digit number.
Important: For the Power BI integration, your Site ID must be formatted as a 6-digit number with leading zeros:
- If your URL shows 226.waitwell.ca, your Site ID is 000226
- If your URL shows 5190.waitwell.ca, your Site ID is 005190
- If your URL shows 61279.waitwell.ca, your Site ID is 061279
- If your URL shows 401921.waitwell.ca, your Site ID remains 401921
If you have difficulty locating this information, contact WaitWell support for assistance.

Note: If you have multiple locations with different Site IDs and need specific IDs for different Power BI uses, contact WaitWell support to provide a complete list of Site IDs.

Step 2: Generate API Key

To generate your API key:

Log into your WaitWell staff account
Navigate to the Staff module and select staff account
Click Generate Key to populate a new API key
Important: The API key will inherit the same access permissions as your staff account

Recommendation: For this integration, use either:

A Staff Record with Admin Access, or
A dedicated dummy account (which can be renamed) with fake credentials and Admin access created purely for the integration

Step 3: Configure Power BI Script

Open Microsoft Power BI Desktop
Go to Get Data > Blank Query
Open the Advanced Editor
Replace the default query with the WaitWell integration script (provided separately)
Update the script with your specific credentials:
- Enter your Site ID at the top of the script
- Enter your API Key at the top of the script

Power BI Script

Below is the complete Power BI script for connecting to WaitWell. Copy this script and paste it into the Power BI Advanced Editor:

let
    pageSize = 1000,        // chunk to fetch at a time
    SiteID = "000201",      // site ID (location)  - Set this
    APIKEY="xxxxxxxxx",  // api key from staff form - Set this
    URL = "https://api.waitwell.ca/api/" & SiteID & "/tickets", // Change the .ca to .us if applicable

    APICall = 

        List.Generate
        (  
            () => [ 
		    APIData = Json.Document(Web.Contents(URL & "?limit="& Text.From(pageSize),  
   			    [Headers=[#"x-apikey"=APIKEY] ] 
		        )),
                start = 0,
                total = APIData[total]
	        ],
            each [start] < Number.From([total]),
           
            each [
                start = [start] + pageSize,
			    APIData = Json.Document(Web.Contents(URL & "?limit=" & Text.From(pageSize) &"&start="& Text.From(start), 
   				    [Headers=[#"x-apikey"=APIKEY] ] 
			    )),
                total = APIData[total]
 		    ],
            each [APIData]
        ),
    #"Converted to Table" = Table.FromList(APICall, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"rows"}, {"rows"}),
    #"Expanded rows" = Table.ExpandListColumn(#"Expanded Column1", "rows"),
    #"Expanded rows1" = Table.ExpandRecordColumn(#"Expanded rows", "rows", {"id", "Created", "CreatedBy", "LastModified", "LastModifiedBy", "TicketNumber", "Date", "Source", "FirstName", "LastName", "PhoneNumber", "Email", "Address", "Lang", "Staff_id", "Location_id", "Queue_id", "Priority", "Stage", "WaitTimeStart", "LastStageChange", "Completed", "Reopened", "Duration1", "Duration2", "Duration3", "InitialPosition", "InitialEstimatedWaitTime", "MoreTime", "MoreTimeStart", "MoreTimeEnd", "TicketLink", "Note", "HasIncomingMsg", "Waiver_id", "ForgotNotificationSent", "ThanksSent", "PartySize", "ServiceType_id", "Customer_id", "Visitor_id", "Availability_id", "AptStartTime", "AptEndTime", "SendingNumber", "CustomField1", "CustomField2", "CustomField3", "CustomField4", "CustomField5", "AptMaxQty", "AppToken", "ReAnnounce", "ClientOnHoldEvents", "Override", "MeetOnline", "MeetingUrl", "MeetingHostUrl", "MeetingId", "TimeTrigger1", "TimeTrigger2", "MeetMethod", "QueueName", "StatusLink", "QueueColor", "QueueColorHex", "CreatedLocalTime", "WaitTimeStartLocalTime", "CompletedLocalTime", "ServeTimeSec", "ActualWaitTimeMins", "InitialEstimatedWaitTimeMins", "StaffName", "LocationName", "CustomKeyword", "Category", "DeskName", "TicketType", "Desk_id", "ServiceType", "NextServiceType_id", "AdditionalServices_id", "AdditionalServices", "NumAttachments", "Rating", "WaiverAlert", "Last4Digits", "Status", "MethodName", "SourceName"}, {"id", "Created", "CreatedBy", "LastModified", "LastModifiedBy", "TicketNumber", "Date", "Source", "FirstName", "LastName", "PhoneNumber", "Email", "Address", "Lang", "Staff_id", "Location_id", "Queue_id", "Priority", "Stage", "WaitTimeStart", "LastStageChange", "Completed", "Reopened", "Duration1", "Duration2", "Duration3", "InitialPosition", "InitialEstimatedWaitTime", "MoreTime", "MoreTimeStart", "MoreTimeEnd", "TicketLink", "Note", "HasIncomingMsg", "Waiver_id", "ForgotNotificationSent", "ThanksSent", "PartySize", "ServiceType_id", "Customer_id", "Visitor_id", "Availability_id", "AptStartTime", "AptEndTime", "SendingNumber", "CustomField1", "CustomField2", "CustomField3", "CustomField4", "CustomField5", "AptMaxQty", "AppToken", "ReAnnounce", "ClientOnHoldEvents", "Override", "MeetOnline", "MeetingUrl", "MeetingHostUrl", "MeetingId", "TimeTrigger1", "TimeTrigger2", "MeetMethod", "QueueName", "StatusLink", "QueueColor", "QueueColorHex", "CreatedLocalTime", "WaitTimeStartLocalTime", "CompletedLocalTime", "ServeTimeSec", "ActualWaitTimeMins", "InitialEstimatedWaitTimeMins", "StaffName", "LocationName", "CustomKeyword", "Category", "DeskName", "TicketType", "Desk_id", "ServiceType", "NextServiceType_id", "AdditionalServices_id", "AdditionalServices", "NumAttachments", "Rating", "WaiverAlert", "Last4Digits", "Status", "MethodName", "SourceName"}),
    #"Sorted Rows" = Table.Sort(#"Expanded rows1",{{"id", Order.Ascending}})
in
    #"Sorted Rows"

Important: Make sure to update the following values in the script:

Replace "000201" with your actual Site ID
Replace "xxxxxxxxx" with your actual API key
Change .ca to .us in the URL if you're using the US version of WaitWell

API Documentation

For additional technical details and API reference information, consult the WaitWell API documentation:

Canadian Version: https://apidoc.waitwell.ca/
US Version: https://apidoc.waitwell.us/

How the Integration Works

The Power BI script handles WaitWell's API pagination automatically by:

Making sequential API calls to retrieve data in "chunks"
Combining all chunks into a complete dataset
Formatting the data for optimal Power BI analysis
Managing API rate limits and error handling

Once the data is imported, you can:

Analyze ticket patterns and trends
Create visualizations and dashboards
Export reports for stakeholders
Combine WaitWell data with other data sources

Troubleshooting

Common Issues

Authentication Errors

Verify your API key is correctly entered in the script
Ensure your staff account has appropriate permissions
Check that the Site ID matches your WaitWell instance

Data Import Failures

Confirm your internet connection is stable
Verify the WaitWell API is accessible
Check for any API rate limit restrictions

Missing Data

Ensure your API key has access to all required ticket data
Verify the date range parameters in your script
Check staff account permissions for data visibility

Microsoft Outlook Calendar Integration (Legacy)

WaitWell APIs for Embedding Estimated Wait Times & Number of Visitors in Queues into Websites

Microsoft Outlook Calendar Integration

Waillo Chat on External Sites

Generating a WaitWell API Key

Power BI Integration