Connect API through Power Query

Power Query - get started

Prerequisites for using Power Query :

1. 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. 

1. Open Excel and navigate to 'Data' bookmark and click 'Get Data (Power Query)', then select 'Blank Query'.
   
   
   
2. 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"
   

   
   
   
3. 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
   1. Getting SimplAuth Token
   2. Getting data from API with the token from the previous sub-step