How to connect to HRConnect API through Power Query

Modified on Fri, 4 Jul at 6:04 PM

Example on how to connect API through Power Query using Excel or Power BI using SimplAuth token authorization.

For detailed Swagger documentation for HRConnect API, visit Simployer Classic HRConnect API
For documentation about SimplAuth API authentication, visit: SimplAuth

Power Query - get started

Prerequisites for using Power Query :

You need to have created a Client in the Admin Center to generate SimplAuth authorization tokens used in Power Query to get data from APIs.

Connection to APIs through Power Query

The following example will be based on Excel Power Query and HRConnect API. SimplAuth tokens have a lifespan set to 1 hour. To avoid the necessity for refreshing tokens every hour, we can set our integration to generate a new token for each data refreshment.

Open Excel and navigate to 'Data' bookmark and click 'Get Data (Power Query)', then select 'Blank Query'.
Paste the following piece of code and remember to change line 2,3 to provide you real ClientId and ClientSecret

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"

You might need to go to 'Options' -> 'Privacy' and check the option "Allow combining data for multiple sources". It is required because of having two HTTP connections

Getting SimplAuth Token
Getting data from API with the token from the previous sub-step