Power BI integration

Import your time entries from TrackingTime into Power BI

Eric Tulle avatar
Written by Eric Tulle
Updated over a week ago

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

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
  • 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
    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 > Clicl "To Table"

3. Expand Records to columns

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

4. Change the column names

Now you 'll see your data as a table. And you have to change the column names.

  • Right click in the query > Advance editor

  • Paste the code bellow.

  • Click save

= Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "s", "e", "d", "se", "seid", "c", "cid", "p", "pid", "pn", "pif", "t", "tid", "tt", "tv", "tl", "dd", "et", "a", "u", "uid", "uhr", "uhc", "uau", "tz", "n", "co", "r", "b", "bd", "to", "er", "rf", "re", "rgi", "ety", "uci", "cci", "sci", "has_files", "toi", "users"}, {"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"})

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/min?include_custom_fields=true&filter=COMPANY&from=2016-01-01&to=2030-01-01&page=" & Text.From(page) & "&page_size=1000", [Headers=[#"Content-Type"="application/json"]])),
#"Converted to Table1" = Record.ToTable(Source),
Value = #"Converted to Table1"{1}[Value],
#"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "s", "e", "d", "se", "seid", "c", "cid", "p", "pid", "pn", "pif", "t", "tid", "tt", "tv", "tl", "dd", "et", "a", "u", "uid", "uhr", "uhc", "uau", "tz", "n", "co", "r", "b", "bd", "to", "er", "rf", "re", "rgi", "ety", "uci", "cci", "sci", "has_files", "toi", "users"}, {"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"})
in
#"Expanded Column1"
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

Step 7: Create Visualizations

With your web data now imported into Power BI, you can proceed to create visualizations, analyze the data, and build reports to gain insights.

Did this answer your question?