Connecting Power BI with Factorial will allow for querying and analyzing all the company information exposed in Factorial’s public API.

How to integrate Power BI with Factorial

Using the https://apidoc.factorialhr.com/reference is the only supported method for integrating with Power BI.

Getting Started

1. On your sidebar, go to Settings. Scroll down to Advanced configuration and click on API keys. Create a new API key
2. Head to PowerBI Desktop and open the Power Query editor
   • Click "Get Data" > “More” > “Blank Query”. Copy and paste into the query editor into a “blank query” the 3 power BI scripts (see below). Each script is a function (a new query).

3. Once you have the three functions: GetPage, GetAllPages and ListDataToTable you invoke them with your apikey and baseURL. The baseURL you retrieved from the reference website of the Factorial public API (Welcome to development with Factorial! ). For each resource (like Employees, ContractVersions, Shifts, etc …) you do one invocation of the function ListDataToTable passing the apikey and the url of the API endpoint (like https://api.factorialhr.com/api/2024-10-01/resources/attendance/shifts). What you get is one table per resource that you can later use to create your report in powerBI. Once you have the resources that you want, you can save this queries with the save button in case you later want to modify them (for instance to add a new resource) and finally you “Close & Apply” to exit the Power Query Editor. With this step, your data is ready to be used in powerBI.

4. That's all. Now you can visualize the data in the Data View or the Report View, in both cases you use the right-hand side menu to add columns from the invoked functions that you did in step 3.

Power BI Scripts

1. Get a page of data from the public API from a resource - GetPage

= (baseURL as text, apiKey as text, page as number) =>
let 
    Url = baseURL & "?page=" & Text.From(page),
    Response = Json.Document(Web.Contents(Url,  [Headers=[#"x-api-key"= apiKey ]])),
    Data = Response[data],
    HasNextPage = Response[meta][has_next_page]
in
    [Data = Data, HasNextPage = HasNextPage]
    

2. Recursively retrieve all the pages - GetAllPages

= let
    Source = (baseURL as text, apiKey as text) =>
let 
  GetAllPages  = (baseURL as text, apiKey as text, page as number, AccumulatedData as list) =>
        let
            CurrentPage = GetPage(baseURL, apiKey, page),
            NewData = List.Combine({AccumulatedData, CurrentPage[Data]}),
            NextStep = if CurrentPage[HasNextPage] then @GetAllPages(baseURL, apiKey, page + 1, NewData) else NewData
        in
            NextStep,
     AllData = GetAllPages(baseURL, apiKey, 1, {})
 in AllData
in
    Source

3. Transform the list data into a table - ListDataToTable

= let
    Source = (baseURL as text, apiKey as text) =>
let
  TableData = Table.FromList(GetAllPages(baseURL, apiKey), Splitter.SplitByNothing(), null, null)
in TableData
in
    Source