Setup using a Power BI file

Step 1: Download the Power Bi (download here)

Step 2: Open the file with Power Bi

Step 3: Go to TrackingTime and generate an app password (click here)

Step 4: Go back to Power Bi and put in user name : API_TOKEN and in the password the one generated in the Step 4

Download PBIT file

Manual setup

Step 1: Launch Power BI Desktop on your computer

Launch Power BI Desktop on your computer if you haven't already.

Step 2: Get Data From Web

Click on "Home" in the top menu.
Select "Get Data".
In the "Get Data" window, scroll down and choose "Web".

Step 3: Configure the Web Data Source

On the top, select the advanced option and fill the form.

URL parts (1)

https://api.trackingtime.co/api/v4/events/flat

URL parts (2)

?include_custom_fields=true&filter=COMPANY&from=2018-01-01&to=2030-01-01

HTTP request header:
```
application/json
```

Step 4: Authorize

After you've entered the URL of the web data source and clicked "OK"
Select "Basic"
User name:
```
API_TOKEN
```
Password : Click here and generate and App Password
```
Put the App Password that you generated in TrackingTime
```
Click Connect.

Step 5: Format the Json data into a table

1. Convert the record to data to table

Go to the Convert Tab > Select the data record > Click "Into table"

2. Convert list to table

Select the list > Click on "To Table"

3. Expand Records to columns

Click on the icon by Column1 > then in the pop up click OK

Step 6: Paging (optional)

If you have more than 5000 results, you'll need to implement pagination. To do this, you need to use the advanced editor to implement custom code that transforms the query to retrieve multiple pages and then merge the data into a single dataset.

1. Transform the query in a function to get the results of a single page.

We will create a function called "GetRecordsPerPage"
Right click over the query then select "Advanced Editor"
Paste the following code in the box:


let
    GetRecordsPerPage = (page) => let
        Source = Json.Document(Web.Contents("https://api.trackingtime.co/api/v4/events/flat?include_custom_fields=true&filter=COMPANY&from=2019-01-01&to=2023-10-01&page=" & Text.From(page) & "&page_size=100")),
        #"Converted to Table" = Table.FromRecords({Source}),
        #"Expanded response" = Table.ExpandRecordColumn(#"Converted to Table", "response", {}, {}),
        #"Expanded data" = Table.ExpandListColumn(#"Expanded response", "data"),
        #"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"ID", "Start", "End", "Duration", "Service", "Service Id", "Customer", "Customer Id", "Project", "Project Id", "Project Notes", "Project is favorite", "Task", "Task Id", "Task Type", "Task Visibility", "Task List", "Due Date", "Estimated Time", "Is Archived", "User", "User Id", "User Hourly Rate", "User Hourly Cost", "User Avatar Url", "Timezone", "Notes", "Color", "Hourly Rate", "Is Billable", "Is Billed", "Icalendar Token", "Event Repeat", "Repeat Rrequency", "Repeat Every", "Repeat Group Id", "Event Type", "User Color Index", "Customer Color Index", "Service Color Index", "Has files", "Time Off Id", "Users", "Position (User CF)", "Type (Task CF)", "Status (Task CF)", "Project type (Project CF)", "Hour type (Event CF)", "TIMELINE_ROW (Task CF)", "TIMELINE_START (Task CF)", "billing_info (Customer CF)", "INVOICE_ID (Event CF)", "ID (Customer CF)", "Location (Event CF)", "Reviewed (Task CF)", "Branch (Task CF)", "Start Date (User CF)", "PROJECT_TIMELINE_ROW (Project CF)", "PROJECT_TIMELINE_START (Project CF)", "Hiring Date (User CF)", "sec_severity (Task CF)", "sec_risk (Task CF)", "sec_effort (Task CF)", "Status (Event CF)", "Combos (Event CF)", "Location (Customer CF)", "Currencysss (Project CF)", "Third Party ID (Task CF)", "Third Party Service (Task CF)", "effort (Customer CF)", "Effort (Task CF)", "Effort (Project CF)", "Efforts (Event CF)", "Third Party ID (Event CF)", "Third Party Service (Event CF)", "Estimation (Task CF)", "Branch (Project CF)", "ID (Event CF)", "Completed (Task CF)", "CALENDAR_SYNC_EVENT (Event CF)", "CALENDAR_SYNC_TASK (Task CF)"}, {"data.ID", "data.Start", "data.End", "data.Duration", "data.Service", "data.Service Id", "data.Customer", "data.Customer Id", "data.Project", "data.Project Id", "data.Project Notes", "data.Project is favorite", "data.Task", "data.Task Id", "data.Task Type", "data.Task Visibility", "data.Task List", "data.Due Date", "data.Estimated Time", "data.Is Archived", "data.User", "data.User Id", "data.User Hourly Rate", "data.User Hourly Cost", "data.User Avatar Url", "data.Timezone", "data.Notes", "data.Color", "data.Hourly Rate", "data.Is Billable", "data.Is Billed", "data.Icalendar Token", "data.Event Repeat", "data.Repeat Rrequency", "data.Repeat Every", "data.Repeat Group Id", "data.Event Type", "data.User Color Index", "data.Customer Color Index", "data.Service Color Index", "data.Has files", "data.Time Off Id", "data.Users", "data.Position (User CF)", "data.Type (Task CF)", "data.Status (Task CF)", "data.Project type (Project CF)", "data.Hour type (Event CF)", "data.TIMELINE_ROW (Task CF)", "data.TIMELINE_START (Task CF)", "data.billing_info (Customer CF)", "data.INVOICE_ID (Event CF)", "data.ID (Customer CF)", "data.Location (Event CF)", "data.Reviewed (Task CF)", "data.Branch (Task CF)", "data.Start Date (User CF)", "data.PROJECT_TIMELINE_ROW (Project CF)", "data.PROJECT_TIMELINE_START (Project CF)", "data.Hiring Date (User CF)", "data.sec_severity (Task CF)", "data.sec_risk (Task CF)", "data.sec_effort (Task CF)", "data.Status (Event CF)", "data.Combos (Event CF)", "data.Location (Customer CF)", "data.Currencysss (Project CF)", "data.Third Party ID (Task CF)", "data.Third Party Service (Task CF)", "data.effort (Customer CF)", "data.Effort (Task CF)", "data.Effort (Project CF)", "data.Efforts (Event CF)", "data.Third Party ID (Event CF)", "data.Third Party Service (Event CF)", "data.Estimation (Task CF)", "data.Branch (Project CF)", "data.ID (Event CF)", "data.Completed (Task CF)", "data.CALENDAR_SYNC_EVENT (Event CF)", "data.CALENDAR_SYNC_TASK (Task CF)"}),
        #"Expanded users" = Table.ExpandRecordColumn(#"Expanded data1", "users", {}, {}),
        #"Expanded schedule_assignments" = Table.ExpandRecordColumn(#"Expanded users", "schedule_assignments", {}, {}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded schedule_assignments",{ {"data.ID", Int64.Type}, {"data.Start", type datetime}, {"data.End", type datetime}, {"data.Duration", Int64.Type}, {"data.Service", type any}, {"data.Service Id", type any}, {"data.Customer", type text}, {"data.Customer Id", Int64.Type}, {"data.Project", type text}, {"data.Project Id", Int64.Type}, {"data.Project Notes", type any}, {"data.Project is favorite", type logical}, {"data.Task", type text}, {"data.Task Id", Int64.Type}, {"data.Task Type", type text}, {"data.Task Visibility", type any}, {"data.Task List", type text}, {"data.Due Date", type datetime}, {"data.Estimated Time", type any}, {"data.Is Archived", type logical}, {"data.User", type text}, {"data.User Id", Int64.Type}, {"data.User Hourly Rate", Int64.Type}, {"data.User Hourly Cost", Int64.Type}, {"data.User Avatar Url", type text}, {"data.Timezone", type text}, {"data.Notes", type text}, {"data.Color", Int64.Type}, {"data.Hourly Rate", Int64.Type}, {"data.Is Billable", type logical}, {"data.Is Billed", type logical}, {"data.Icalendar Token", type text}, {"data.Event Repeat", type any}, {"data.Repeat Rrequency", type any}, {"data.Repeat Every", type any}, {"data.Repeat Group Id", type any}, {"data.Event Type", type text}, {"data.User Color Index", Int64.Type}, {"data.Customer Color Index", Int64.Type}, {"data.Service Color Index", type any}, {"data.Has files", type logical}, {"data.Time Off Id", type any}, {"data.Users", type any}, {"data.Position (User CF)", type text}, {"data.Type (Task CF)", type text}, {"data.Status (Task CF)", type text}, {"data.Project type (Project CF)", type text}, {"data.Hour type (Event CF)", type text}, {"data.TIMELINE_ROW (Task CF)", Int64.Type}, {"data.TIMELINE_START (Task CF)", Int64.Type}, {"data.billing_info (Customer CF)", type any}, {"data.INVOICE_ID (Event CF)", type any}, {"data.ID (Customer CF)", type any}, {"data.Location (Event CF)", type any}, {"data.Reviewed (Task CF)", type any}, {"data.Branch (Task CF)", type any}, {"data.Start Date (User CF)", type date}, {"data.PROJECT_TIMELINE_ROW (Project CF)", type any}, {"data.PROJECT_TIMELINE_START (Project CF)", type any}, {"data.Hiring Date (User CF)", type any}, {"data.sec_severity (Task CF)", type any}, {"data.sec_risk (Task CF)", type any}, {"data.sec_effort (Task CF)", type any}, {"data.Status (Event CF)", type any}, {"data.Combos (Event CF)", type any}, {"data.Location (Customer CF)", type any}, {"data.Currencysss (Project CF)", type any}, {"data.Third Party ID (Task CF)", type any}, {"data.Third Party Service (Task CF)", type any}, {"data.effort (Customer CF)", type any}, {"data.Effort (Task CF)", type any}, {"data.Effort (Project CF)", type any}, {"data.Efforts (Event CF)", type any}, {"data.Third Party ID (Event CF)", type any}, {"data.Third Party Service (Event CF)", type any}, {"data.Estimation (Task CF)", type any}, {"data.Branch (Project CF)", type any}, {"data.ID (Event CF)", type any}, {"data.Completed (Task CF)", type any}, {"data.CALENDAR_SYNC_EVENT (Event CF)", type any}, {"data.CALENDAR_SYNC_TASK (Task CF)", type any}})
    in
        #"Changed Type"
in
    GetRecordsPerPage

Click done.
Set the name "GetRecordsPerPage".
ok

2. Create a function to get the total amount of Records you have.

We will create a function called "GetRecordsCount"
Right click in the "GetRecordsPerPage" function and click duplicate and click "Edit".
Set the name "GetRecordsCount" and paste the code bellow.

let
    Source = () => let
        Source = Json.Document(Web.Contents("https://api.trackingtime.co/api/v4/events/count?filter=COMPANY&from=2016-01-01&to=2030-01-01", [Headers=[#"Content-Type"="application/json"]])),
        #"Converted to Table1" = Record.ToTable(Source),
        Value = #"Converted to Table1"{1}[Value],
        #"Converted to Table" = Record.ToTable(Value)
    in
        #"Converted to Table"
in
    Source

3. Create a function to merge all pages

We will create a function called "MergePages"
Right click in the "GetRecordsCount" function and click duplicate and click "Edit".
Set the name "MergePages" and paste the code bellow.

let
    NumPages = (List.First(GetCountRecords()[Value]) / 1000) + 1,

    Data = Table.Combine(List.Generate(
        () => [Page = 0, Information = GetRecordsPerPage(0)],
        each [Page] < NumPages,
        each [Page = [Page] + 1, Information = GetRecordsPerPage([Page])],
        each [Information]
    ))

in
    Data