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
-
Open the Power BI application.
The main interface opens.
Choose Blank report
-
Go to the menu and select Blank report.
-
In the Get Data section, select Blank Query. You can also enter Blank Query into the search bar to quickly find it.
Access Power Query Editor
-
Select Advanced Editor.
-
In the editor, you can create an access token using Power Query to gather data from the IRIS HR API's various endpoints.
-
We have provided pseudo code as an example, based on the employee endpoint.
Copylet
// 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,
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, {}),
// Turn Response Data into a Table
#"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","PayrollCompanyName","Initials","AssignedPaySchemes"}, {"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","PayrollCompanyName","Initials","AssignedPaySchemes"}),
// Handle Phones
#"Expanded Phones" = Table.ExpandListColumn(#"Expanded Column1", "Phones"),
#"Expanded Phones Records" = Table.ExpandRecordColumn(#"Expanded Phones", "Phones", {"Ownership", "Type", "Value"}, {"Ownership", "Type", "Value"}),
#"Added CustomPhone" = Table.AddColumn(#"Expanded Phones Records", "PhoneColumn", each
if [Ownership] = "Organization" and [Type] = "Landline" then "OrganizationLandline"
else if [Ownership] = "Organization" and [Type] = "Mobile" then "OrganizationMobile"
else if [Ownership] = "Personal" and [Type] = "Landline" then "PersonalLandline"
else if [Ownership] = "Personal" and [Type] = "Mobile" then "PersonalMobile"
else null
),
#"Filtered PhoneRows" = Table.SelectRows(#"Added CustomPhone", each [PhoneColumn] <> null),
#"Grouped PhoneRows" = Table.Group(#"Filtered PhoneRows",
List.RemoveItems(Table.ColumnNames(#"Filtered PhoneRows"), {"Ownership", "Type", "Value", "PhoneColumn"}),
{
{"OrganizationLandline", each try Table.SelectRows(_, each [PhoneColumn] = "OrganizationLandline")[Value]{0} otherwise null, type text},
{"OrganizationMobile", each try Table.SelectRows(_, each [PhoneColumn] = "OrganizationMobile")[Value]{0} otherwise null, type text},
{"PersonalLandline", each try Table.SelectRows(_, each [PhoneColumn] = "PersonalLandline")[Value]{0} otherwise null, type text},
{"PersonalMobile", each try Table.SelectRows(_, each [PhoneColumn] = "PersonalMobile")[Value]{0} otherwise null, type text}
}),
// Handle Emails
#"Expanded Emails" = Table.ExpandListColumn(#"Grouped PhoneRows", "Emails"),
#"Expanded Emails Records" = Table.ExpandRecordColumn(#"Expanded Emails", "Emails", {"Ownership", "Value"}, {"Ownership", "Value"}),
#"Added CustomEmail" = Table.AddColumn(#"Expanded Emails Records", "EmailColumn", each
if [Ownership] = "Organization" then "OrganizationEmail"
else if [Ownership] = "Personal" then "PersonalEmail"
else null
),
#"Filtered EmailRows" = Table.SelectRows(#"Added CustomEmail" , each [EmailColumn] <> null),
#"Grouped EmailRows" = Table.Group(#"Filtered EmailRows",
List.RemoveItems(Table.ColumnNames(#"Filtered EmailRows"), {"Ownership", "Type", "Value", "EmailColumn"}),
{
{"OrganizationEmail", each try Table.SelectRows(_, each [EmailColumn] = "OrganizationEmail")[Value]{0} otherwise null, type text},
{"PersonalEmail", each try Table.SelectRows(_, each [EmailColumn] = "PersonalEmail")[Value]{0} otherwise null, type text}
}),
// Handle Address
#"Expanded Addresses" = Table.ExpandListColumn(#"Grouped EmailRows", "Addresses"),
#"Expanded Addresses1" = Table.ExpandRecordColumn(#"Expanded Addresses", "Addresses", { "Address1", "Address2", "Address3", "Address4", "Address5", "PostCode"}, {"Address1", "Address2", "Address3", "Address4", "Address5", "PostCode"}),
// Handle Assigned Pay Schemes
#"Expanded AssignedPaySchemes" = Table.ExpandRecordColumn(#"Expanded Addresses1", "AssignedPaySchemes", {"SickPay", "MaternityPay", "PaternityPay", "AdoptionPay", "SharedParentalPay", "AverageHolidayPay", "BereavementPay", "NeonatalPay"}, {"AssignedSickPayScheme", "AssignedMaternityPayScheme", "AssignedPaternityPayScheme", "AssignedAdoptionPayScheme", "AssignedSharedParentalPayScheme", "AssignedAverageHolidayPayScheme", "AssignedBereavementPayScheme", "AssignedNeonatalPayScheme"})
in
#"Expanded AssignedPaySchemes"Run the Query
-
After pasting the code into the editor, select Done.
-
If a data privacy error message appears, select Continue.
Set Security Level
-
Select the appropriate security level.
Learn more about security levels (opens in a new window).
-
This example is based on choosing a Private security level.
-
Your employee data should display in a table, which you can use for dashboard development.
