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
Password : Click here and generate and App PasswordPut 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.