Excel Analytics via PowerQuery API

Follow this guide to create custom reports in a multi-sheet Excel workbook that can connect to paiyroll® via API.

Security #

Important: When you create a workbook as described below, be aware that you will be including your credentials. That means anyone accessing the workbook can read any of the paiyroll® data; therefore, you should not email the workbook or share it with anyone who must not get access to the full payroll data. If you need to share the workbook, then be sure to go into the Power Query editor and clear the Client Id and Secret Applied steps values.

Register a new application #

Create credentials #

The first step in paiyroll® is to register a new application and create credentials. These consist of a Client Id and a Client secret (like a username and password pair).

• Go to create and manage OAuth 2.0 applications
• Click to register a new one
• Before you click Save, you must:
  • Click in the Client ID and the Client secret fields
  • Right-click to copy the strings which are longer than displayed, and
  • Paste them securely into a separate text or Word document – this will be the last time you see them! If you forget or lose the credentials, you must delete the application and repeat this process.
  • Select Confidential and Client credentials

• Click Save
• Close the next browser confirmation tab as the process is complete

You have now registered a new application, and you have an associated Client Id and a Client secret.

Report Definition Id #

The other piece of information you’ll need is the report definition ID. Go to Setup > Report Definitions > Update the Finance Journal. The id will be visible in the browser URL. For example, the Id below is 1234:

Create a linked Excel Workbook #

You are now ready to create and link an Excel workbook via API.

• Open Excel and create a blank workbook.
• Windows: Data > Get Data > From Other Sources > Blank Query > Advanced Editor or
• macOS: Data > Get Data (Power Query) > Blank Query
• Copy/Paste the code below into the query window and overwrite the four-line basic query provided

let
     ClientId = "<Client Id from Swagger application>",
     ClientSecret = "<Client Secret from Swagger application>",
     ReportDefinitionId = "<Report Definition Id>",
     StartDate = "2025-03-01",
     EndDate = "2025-04-01",
     GroupNumbered = "false",
     TokenUrl = "https://login.paiyroll.com/api/auth/token/",
     DataUrl = "https://login.paiyroll.com/api/v1/analytics/" & ReportDefinitionId & "/download/" & "?Pay%20definitions=.*&Group%20numbered=" & GroupNumbered & "&Start%20date=" & StartDate & "&End%20date=" & EndDate,
     Body = "grant_type=client_credentials&client_id=" & ClientId & "&client_secret=" & ClientSecret,
     AuthResponse = Json.Document(
         Web.Contents(TokenUrl, [
             Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
             Content=Text.ToBinary(Body)
         ])
     ),
     Token = AuthResponse[token_type] & " " & AuthResponse[access_token],
     Source = Excel.Workbook(
         Web.Contents(DataUrl, [Headers=[Authorization=Token]]),
         null, true
     ),
  Navigation = Source{[Item = "Employee Details", Kind = "Sheet"]}[Data],
  #"Promoted headers" = Table.PromoteHeaders(Navigation, [PromoteAllScalars = true])
 in
     #"Promoted headers"

• Within the double quotes “”, carefully insert your
  • Client Id
  • Client Secret and
  • Report Definition Id
  • Change the Start and End dates, keeping the date range narrow for speed and performance.
  • Optionally change GroupNumbered to true

The blank query with your data should look like this:

The following steps depend on whether you are running on Windows or macOS.

If you have completed all the steps correctly, you will see the employee report table in PowerQuery.

Click Close and Load

Your workbook has been linked, and you can now create Pivot Tables or PowerQuery sheets to create unlimited reports based on your paiyroll® data.

Using the workbook #

You will be prompted to Enable content each time you open the workbook.

If data changes in paiyroll®, for example, you create additional pay runs, you can refresh the workbook by clicking Refresh < Refresh All.

As time passes and your dates need to be changed, modify the start and end dates in the query settings:

Data > Get Data (Power Query) > Launch Power Query Editor

Select the relevant Query settings, modify the value and click the tick:

Then Close and Load

Deleting Registered Application, Client ID and Client Secret #

At any time, you can delete the registered application, which will also delete the Client Id in the Client secret. This will stop any access from the workbook.

•  Create and manage OAuth 2.0 applications
• Click the application
• Click Delete