Power BI custom connector guide

Absentify Power BI Connector Documentation

Interested in analyzing data trends to optimize operations or efficiently handle employee absences? Whether you're a business owner, HR manager, or an employee looking to optimize your work experience, understanding the capabilities of these tools can significantly impact your success!

With the Microsoft PowerBi Connector offered by absentify, you can easily elevate the way you analyze your absence planning and internal scheduling.

Please note that this feature is only available in the Plus Plan. Interested? See our Plans and pricing to power up your plan and get the most out of what absentify has to offer!

Installing the Connector:

Download the absentify Power BI Custom Connector (.mez) file from the following link: Download Link

Save the downloaded .mez file in the following folder on your computer: Documents\Power BI Desktop\Custom Connectors. If this folder does not exist, please create it manually.

Setting up Power BI to Enable the Connector:

Open Power BI Desktop.

Click on File > Options and settings > Options in the menu bar.

In the left menu, click on Security.

Under Data Extensions, select the Allow any extension to load without validation or warning option.

Click OK. Power BI Desktop might prompt you to restart the application for the changes to take effect.

Creating an API Key in absentify:

Log into absentify.

Navigate to Integrations > API Key.

Create a new API key. Keep this key safe as you will need it in the next step.

Using the absentify Connector:

In Power BI Desktop, click on Home > Get Data.

In the Get Data dialog box, scroll down to Other and select absentify.

Click on Connect.

When prompted for the API key, enter the key you created in the absentify tool.

Upon successful authentication, you may access your absentify data.

Important Note:

Currently, the absentify Power BI Custom Connector cannot be used for scheduled refreshes in the Power BI Service. This means that you will need to manually refresh the data in Power BI Desktop and then upload the updated report to the Power BI Service. To automate data refreshes, consider using a direct API request within Power Query as described below.

Using a Direct API Request with Power Query:

If you need to automate the data refresh process, you can use the following Power Query M code to directly request data from the API with your API key.

Example: Fetching Members Data

Open Power BI Desktop and navigate to the Power Query Editor.

Create a new blank query and open the Advanced Editor.

Replace the existing code with the following M code:

let
    // URL of the API
    apiUrl = "https://api.absentify.com/api/v1/members",

    // API key
    apiKey = "your_api_key_here",

    // Header with API key
    headers = [
        #"X-API-KEY" = apiKey
    ],

    // Perform the API request
    response = try Web.Contents(apiUrl, [Headers = headers]),

    // Check if the API request was successful
    jsonResponse = if response[HasError] then
        error "Error: " & response[Error][Message]
    else
        Json.Document(response[Value]),

    // Check if the response is a list
    responseList = if Type.Is(Value.Type(jsonResponse), List.Type) then jsonResponse else {jsonResponse},

    // Convert the list to a table
    responseTable = Table.FromList(responseList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // Expand columns if the table contains records
    expandedTable = if List.Count(responseList) > 0 and Value.Is(responseList{0}, Record.Type) then
        Table.ExpandRecordColumn(responseTable, "Column1", Record.FieldNames(responseList{0}))
    else
        responseTable
in
    expandedTable

Click Done to save the query.

Load the data into Power BI.

Example: Fetching Requests Per Day

To fetch requests per day, you can use the following M code:

let
    // URL of the API
    apiUrl = "https://api.absentify.com/api/v1/requests_per_day",

    // API key
    apiKey = "your_api_key_here",

    // Parameters (replace with actual GUIDs and date-time values)
    startDate = "2024-01-01T00:00:00Z",  // Example start date
    endDate = "2024-01-31T23:59:59Z",    // Example end date
    status = "APPROVED",                 // Optional status
    requestMemberIds = "guid1,guid2,guid3",  // Optional GUIDs
    departmentIds = "guidA,guidB,guidC",     // Optional GUIDs

    // Header with API key
    headers = [
        #"X-API-KEY" = apiKey
    ],

    // Query parameters
    queryParams = [
        start = startDate,
        end = endDate,
        status = status,
        request_member_ids = requestMemberIds,
        department_ids = departmentIds
    ],

    // Perform the API request
    response = try Web.Contents(apiUrl, [
        Headers = headers,
        Query = queryParams
    ]),

    // Check if the API request was successful
    jsonResponse = if response[HasError] then
        error "Error: " & response[Error][Message]
    else
        Json.Document(response[Value]),

    // Check if the response is a list
    responseList = if Type.Is(Value.Type(jsonResponse), List.Type) then jsonResponse else {jsonResponse},

    // Convert the list to a table
    responseTable = Table.FromList(responseList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // Expand columns if the table contains records
    expandedTable = if List.Count(responseList) > 0 and Value.Is(responseList{0}, Record.Type) then
        Table.ExpandRecordColumn(responseTable, "Column1", Record.FieldNames(responseList{0}))
    else
        responseTable
in
    expandedTable

By using this method, you can refresh your data directly from the API without relying on the Power BI Service's scheduled refresh feature. This ensures that your data remains up-to-date without manual uploads.

Applying the Method to Other API Endpoints:

To use other API endpoints, follow these steps:

Refer to the API Documentation: Visit the API Reference to understand the available endpoints and required parameters.

Update the API URL: Replace the apiUrl value in the M code with the endpoint URL you want to access.

Set Parameters: Adjust the parameters ( queryParams) based on the requirements of the endpoint. Ensure that you use the correct parameter names and data types as specified in the API documentation.

Modify the M Code: Update the M code template to reflect the new endpoint and parameters. Here's a generalized template:

let
    // URL of the API
    apiUrl = "https://api.absentify.com/api/v1/your_endpoint_here",

    // API key
    apiKey = "your_api_key_here",

    // Parameters (adjust based on the endpoint requirements)
    param1 = "value1",
    param2 = "value2",
    // Add more parameters as needed

    // Header with API key
    headers = [
        #"X-API-KEY" = apiKey
    ],

    // Query parameters
    queryParams = [
        param1 = param1,
        param2 = param2
        // Add more parameters as needed
    ],

    // Perform the API request
    response = try Web.Contents(apiUrl, [
        Headers = headers,
        Query = queryParams
    ]),

    // Check if the API request was successful
    jsonResponse = if response[HasError] then
        error "Error: " & response[Error][Message]
    else
        Json.Document(response[Value]),

    // Check if the response is a list
    responseList = if Type.Is(Value.Type(jsonResponse), List.Type) then jsonResponse else {jsonResponse},

    // Convert the list to a table
    responseTable = Table.FromList(responseList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // Expand columns if the table

 contains records
    expandedTable = if List.Count(responseList) > 0 and Value.Is(responseList{0}, Record.Type) then
        Table.ExpandRecordColumn(responseTable, "Column1", Record.FieldNames(responseList{0}))
    else
        responseTable
in
    expandedTable

Related:

Plans and pricing
Detailed guide to our plans
API Reference

By following these steps and using the provided examples, you can make the most of the absentify Power BI integration, whether you are using the custom connector or directly querying the API.

Updated on: 11/11/2024