Connect the HR API to Power BI: example

While we do not provide support for Power BI development, as it is not an IRIS product, we understand many businesses use it and want to integrate it with the IRIS HR API.

This topic provides an example of how to integrate the IRIS HR API into Power BI Desktop. However, it is not a complete solution, although, you can review the example and build on it. Due to this being a Dynamic Query it may not be compatible with Power BI Web.

This method uses Microsoft's Power Query (opens in a new window).

The content of this topic is for informational purposes only. We cannot provide any additional support in using Power BI with the IRIS HR API.

Employee End point example

Launch Power BI

  1. Open the Power BI application.

    The main interface opens.

    Choose Blank report

  2. Go to the menu and select Blank report.

    The Blank report option.

  3. In the Get Data section, select Blank Query. You can also enter Blank Query into the search bar to quickly find it.

    The Blank Query option.

    Access Power Query Editor

  4. Select Advanced Editor.

  5. In the editor, you can create an access token using Power Query to gather data from the IRIS HR API's various endpoints.

    The Advanced Editor.  

  6. We have provided pseudo code as an example, based on the employee endpoint.

    Copy
    let
        // First, get the access token
        tokenUrl = "https://api.iris.co.uk/oauth2/v1/token",
        tokenHeaders = [
            #"Content-Type" = "application/x-www-form-urlencoded",
            #"Authorization" = "Basic {Your credentials in Base64}"
        ],
        tokenBody = "grant_type=client_credentials",

        // Function to HTTP POST request for token
        GetJsonToken = (url as text, body as text, headers as record) as record =>
            let
                response = Web.Contents(url, [
                    Headers = headers,
                    Content = Text.ToBinary(body)
                ]),
                json = Json.Document(response)
            in
                json,

        // Call the function and retrieve the token data
        tokenResult = GetJsonToken(tokenUrl, tokenBody, tokenHeaders),
        access = tokenResult[access_token],

        // Function to handle the HTTP GET request for employees
        GetEmployeeData = (url as text, headers as record) as record =>
            let
                response = Web.Contents(url, [Headers = headers]),
                json = Json.Document(response)
            in
                json,

        // Function to recursively get all pages of employee data
        GetAllEmployeeData = (url as text, headers as record, optional accumulatedData as list) as list =>
            let
                result = GetEmployeeData(url, headers),
                nextLink = try result[#"@odata.nextLink"] otherwise null,
                data = try result[value] otherwise null,
                newAccumulatedData = List.Combine({accumulatedData, data}),
                finalData = if nextLink <> null then
                                @GetAllEmployeeData(nextLink, headers, newAccumulatedData)
                            else
                                newAccumulatedData
            in
                finalData,

        // Initial call to get all employee data
        employeeUrl = "https://api.iris.co.uk/hr/v2/employees",
        employeeHeaders = [
            #"Accept" = "application/json;odata.metadata=minimal;odata=true;version1",
            #"Authorization" = "Bearer " & access
        ],
        allEmployeeData = GetAllEmployeeData(employeeUrl, employeeHeaders, {}),
        #"Converted to Table" = Table.FromList(allEmployeeData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Id", "SourceSystemId", "CreatedOn", "SourceSystemCreatedOn", "CreatedBy", "LastModifiedOn", "SourceSystemLastModifiedOn", "LastModifiedBy", "GenderIdentity", "WindowsUsername", "DisplayId", "TitleHonorific", "FirstName", "KnownAs", "OtherName", "LastName", "CostCentre", "WorkingStatus", "IsManager", "NationalInsuranceNumber", "PayrollId", "TaxCode", "IncludeInPayroll", "EmploymentStartDate", "EmploymentLeftDate", "ContinuousServiceDate", "DateOfBirth", "LastWorkingDate", "Gender", "Ethnicity", "Nationality", "Religion", "LeaverReason", "MaritalStatus", "Phones", "Emails", "Addresses"}, {"Id", "SourceSystemId", "CreatedOn", "SourceSystemCreatedOn", "CreatedBy", "LastModifiedOn", "SourceSystemLastModifiedOn", "LastModifiedBy", "GenderIdentity", "WindowsUsername", "DisplayId", "TitleHonorific", "FirstName", "KnownAs", "OtherName", "LastName", "CostCentre", "WorkingStatus", "IsManager", "NationalInsuranceNumber", "PayrollId", "TaxCode", "IncludeInPayroll", "EmploymentStartDate", "EmploymentLeftDate", "ContinuousServiceDate", "DateOfBirth", "LastWorkingDate", "Gender", "Ethnicity", "Nationality", "Religion", "LeaverReason", "MaritalStatus", "Phones", "Emails", "Addresses"})
    in
        #"Expanded Column1"

    Run the Query

  7. After pasting the code into the editor, select Done.

  8. If a data privacy error message appears, select Continue.

    The data privacy error message.

    Set Security Level

  9. Select the appropriate security level.

    Learn more about security levels (opens in a new window).

  10. This example is based on choosing a Private security level.

  11. Your employee data should display in a table, which you can use for dashboard development.

    Employee data displaed in a table.