Use Power Query in Excel or Power BI to retrieve HRConnect API data with SimplAuth token authentication.

This example shows how to connect to the HRConnect API using Power Query in Excel or Power BI with SimplAuth token authentication.

For detailed Swagger documentation for the HRConnect API, see the article "Simployer Classic HRConnect API." For documentation about SimplAuth API authentication, see the article about SimplAuth.

Prerequisites for connecting to the HRConnect API

You must create a client in Admin Center to generate the SimplAuth authorization tokens that Power Query uses to retrieve data from the HRConnect API.

Connect to the HRConnect API using Power Query

The following example uses Excel Power Query and the HRConnect API. SimplAuth tokens are valid for one hour. To avoid renewing the token manually every hour, you can configure the integration to generate a new token each time the data is refreshed.

1. Open Excel and go to the Data tab.
2. Select Get Data (Power Query), and then select Blank Query.
3. Paste the following code. Replace YOUR_CLIENT_SECRET and YOUR_CLIENT_ID with your own ClientSecret and ClientId:

let    url = "https://simplauth.simployer.com/oauth/token",    clientSecret = "YOUR_CLIENT_SECRET",    clientId = "YOUR_CLIENT_ID",    headers = [#"Content-Type" = "application/json"],    postData = Text.Combine({        "{ ""client_id"": """, clientId,        """, ""client_secret"": """, clientSecret,        """, ""audience"": ""https://hrconnect.simployer.com"",        """, ""grant_type"": ""client_credentials"" }"    }),    response = Web.Contents(        url,        [            Headers = headers,            Content = Text.ToBinary(postData)        ]    ),    jsonResponse = let        token = Record.Field(Json.Document(response), "access_token"),        responseData = Json.Document(            Web.Contents(                "https://hrconnect.simployer.com/v1/persons?page=1&pageSize=10000",                [Headers = [Authorization = token]]            )        )    in        responseData,    #"Converted to table" = Table.FromList(        jsonResponse,        Splitter.SplitByNothing(),        null,        null,        ExtraValues.Error    ),    #"Expanded Column1" = Table.ExpandRecordColumn(        #"Converted to table",        "Column1",        {"id", "firstName", "lastName", "nickName", "birthdate", "seniorityDate", "seniorityMonths", "bankAccount1", "iban1", "bankAccount2", "iban2", "bankCountry1", "bankCountry2", "sex", "nationality", "active", "affiliatedOrganizationId", "primaryPhone", "primaryEmail"},        {"id", "firstName", "lastName", "nickName", "birthdate", "seniorityDate", "seniorityMonths", "bankAccount1", "iban1", "bankAccount2", "iban2", "bankCountry1", "bankCountry2", "sex", "nationality", "active", "affiliatedOrganizationId", "primaryPhone", "primaryEmail"}    ) in    #"Expanded Column1"

When the query runs, Power Query retrieves person data from the HRConnect API and returns the selected fields as a table.

Allow Power Query to combine SimplAuth and HRConnect API data

You may need to go to Options > Privacy and enable Allow combining data for multiple sources.

This setting is required because the Power Query setup uses two HTTP connections:

• One connection retrieves the SimplAuth token
• One connection retrieves data from the HRConnect API using the token