Power BI Data flow manual

It is possible to visualise data from ClockWise on the online version of Power BI. This can be done using a data flow. In this manual, it will be explained how to configure one to get the correct data from ClockWise.

1. Making an API client

In order to work with Power BI, there needs to be an API-client. This can be made in Configuration → Settigns → OAuth/API clients. Click on "Add OAuth Client". Give a recognisable name to the client, which indicates what it is used for (e.g. Power BI), and fill in the email address of the connection administrator. Press Save.

Put the option "Access token expires" on "Never". The access token we will get in a later step will be used indefinitely.

2. Making an authorisation code

Note: The authorisation code is only valid for an hour. Make sure the next step(s) are done within an hour, otherwise they need to be done again!

We need an autorisation code. For this, go to the OAuth client and click on "Request Authorization Code (api/v2/auth)".
A pop-up appears with a filled in "Response type", "Redirect URI" and "Clinet ID". Those shouldn't be changed. Click "Request".

You are redirected to the login page of ClockWise. Log in as usual.

After logging in, a new page is shown. On the page, you can see a code on the bottom.

This code is needed in the next step

3. Getting an access token

We now need an access token. This works similarly as previous step, but now the button "Request Tokens" is needed.

The default settings are correct again, but there is one field that needs to be filled in: The autorisation code from last step.
Click on "Request"

A window opens with an access_token, refresh_token and some other information. The access_token is needed for the next steps.

This token is valid indefinitely, due to the "Access token expires" from the first step

4. Preparing the query

The authorisation code is not needed anymore, so the time constraint of one hour doesn't apply anymore

Download the file ServerDataConnector.m from the site. Open it in a text editor.

In this file, and in this manual, the text "uwomgevingnaam", or "yourenvironmentname" is used. Replace those with the name of your ClockWise environment.

Furthermore, on line 3, there is the tekst **access token** between double quotes. Replace that (including the asterisks, excluding the quotes) with the access token from previous step.

If desired, you can give parameters to the different tables, comparable with the parameters of the ClockWise Data connector.
These can be configured in the Contents() function call on line 228. This function has a few parameters:

• The first one is whether the rates should be tarieved (true or false, false by default)
• The parameters after that are the extra arguments for respectively the hours, resources, projects and employee tables. Those need to be provided between double quotes ("), as URL-parameter (e.g. "all_projects=true&project_name_split=1,1"). Explanation of those fields can be found in the API documentation. For this, go to "yourenvironmentname.clockwise.info/api/v2/doc" → Reports → "Hours in flat format", where yourenvironmentname is the name of the ClockWise environment.

Save the changes.

5. Initialising Power BI

Now we can make the data flow

Log in the browser in the online environment of Power BI, and go to the workspace where the data flow will be made.

Note: This is not possible in a personal work space

• Click on "+ New" → "Data flow"
• Click on "Define new tables" (Option on the left side)
• A page with data sources is shown. One of them at the bottom is called "Empty query". Click on it

A query is shown with a text like

let
  Source=""
in
  Source

Leave this, and click on Next.

The Power Query editor is now opened. Power BI really likes their query to yield a table, so you are sent to the text tools tab.

• Click on "To table" to work around this.
• Then go to the "Start" tab, and click on "Advanced editor" at "Query".

The editor that opens looks like the previous one, with a few extra lines.

• Remove everything here.
• Copy the contents of the file from the previous step (after the changes) in the text area.
• Click on OK

Important

With the right mouse button, click on query (in the left column), and make sure the checkbox at "Loading enabled" is unchecked! There are complicated types in the query, and Power BI can't use them at the moment.

6. Filling queries with the information from ClockWise

We are now using this query to get the different tables from the data flow.

• Right click the 'Query' again under 'Queries [1]'
• Select "Reference"

A query with the name "Query (...)" is made (with a number between the brackets)

• Click on the newly made Query (when it is loaded) on the tekst '[Table]' next to the cell with "Employees" (or the table your are interested in). After a bit of calculating, the employee table is shown.

Do these three steps another 3 times, for resp. "Projects", "Resource" and "Hours".
Note: Make sure each of those selections are made for the new "Query(...)", and not for the original "Query"! Othewise, the other queries can't retrieve their data, as the all refer to the original "Query".
Tip! It may be clearer when those queries are renamed to the table that is selected there. E.g. "Query (2)" could be renamed to "Employee". This is possible in the column at the right.

The data flow can now be saved. Validation can take a while, no worries when it takes 15 or so minutes.

Important

Sometimes, Power BI asks a couple of things during the validation process:

• When Power BI says that multiple data sources can see data of each other, click on OK (All data is from ClockWise anyway)
• Sometimes, Power BI asks how you want the data to be authenticated. The default 'No authorisation' is correct. (The authorisation is done by the script itself, but Power BI doesn't know that). If the question is asked multiple times right after each other, (especially for "yourenvironmentname.clockwise.info/api/v2"), it helps to click "Refresh" on the top.
• It is possible that there are problems with some security settings, which prevent data from being retrieved. Go the that query, and turn the security settings off.

Links between the tables

The four tables have references to each other, which allows them to be linked in the dataflow.
These resources are:
- hours.resource_id ↔ resources.resource_id
- resources.employee_id ↔ employees.employee_id
- resources.project_id ↔ projects.project_id

Overview of the most commonly used fields

The following table gives an explanation of the most used fields in Power BI. Most of them will be self-explanatory.
As a general rule of thumb, the id fields are not necessary for graphing purposes. A few of those are needed to connect the four different tables to each other, but those relations should be there already (see the 'Link between the tables' section above).

There are other similar fields to the ones listed above, for instance project_code_path, yielding a path of project codes instead of project names. These are not listed to keep the table short.
Keep in mind that (in the last example) the similar named project_id_path is probably not the field to use, unless a connection with another table (with project entries) is needed.

Advanced: One step further with tables

It is also possible to get different tables or endpoints in a dataflow, similarly to the GetTable function in the advanced section of the ClockWise Data connector manual. This is done with the ParsedTable(url) function.
There are two ways to do this:

• In a new query in the data flow, the "ServerDataConnector.m" from step 4 can be reused. Instead of the line Bron = Contents() on line 228, the line should be replaced with Bron = ParsedTabe(url) (with url being a double quoted string, containing the url of the endpoint. For instance Bron = ParsedTable("/report/flat/planning?params=here").
• The other option is to use the file ServerDataConnector_gettable.m. This file needs similar modifications as were done in step 4.
  • Furthermore, on line 83, the URL can be filled in in the GetTable function. Aforementioned example is already filled in there.
  • Paste this file in a new query in the data flow. Loading the table works similarly to step 6.

It is possible that Power Bi shows warnings about some columns not having a type. This can be solved in the tab "Transform"

• Select a column with an icon 'ABC 123', and select the data type 'Text'. (This is a good default type when Power BI can't find the type)
• If it is entirely clear that the column just contains numbers, the types integer or decimal could be used as well

Note: The data flow cannot be saved while there are errors about columns without types!

Case: Summing hours on sub project level versus budgetted hours on that level

This case we show how the Contents function would need to be called. For this, we use an example that we are going to make statistics on sub project level.
The following structure is used as clarification:

We want to use the summed hours in "Documentation", "Technic" and "Website" versus the data on those projects.
In order for those data to be retrieved, we need to give the following parameters to Power BI:

• hour parameters: e.g. start=2020-01-01.
• resource parameters: Nothing
• project parameters: allprojects=true&project_name_split=1,1&project_id_split=1,1
• employee parameters: Nothing

Line 228 would then look like this:

Bron = Contents(false, "start=2020-01-01", "", "allprojects=true&project_name_split=1,1&project_id_split=1,1", "")

Setting up an incremental refresh

To save data from the API, it is possble to refresh the data incrementally

Note: This functionality is only available in Premium workspaces

Microsoft wrote documentation about this: Using incremental refresh with dataflows

Frequently asked questions

How do the parameters of the Contents function work?

The first parameter is either true or false, and indicates whether rate information should be retrieved as well.
The rest are in url-parameter format: They are notated as key=value, where key is one of the values in the API-documentation (which you can find on youraccountnaam.clockwise.info/api/v2/doc → Reports → "Hours in flat format"), and value the corresponding value.
Different key-value pairs are joined together with &. Also look at the example in the case above.

The fields parameter (which is used to retrieve specifiv fields): These are comma separated. (E.g. fields=name,code,type). To turn fields off, you can prepend an exclamation mark (E.g. fields=name,code,!type). There are also a few field groups, which can be used. These can be turned on and off on the same way (E.g. fields=name,projectfields,!otherfields,!type).
A few of the fields are already retrieved by default! It can be useful to first look which fields are retrieved, and then modify the parameters of the Contents function to turn fields on and off, as needed.

Is it also possible to use ClockWise data on Power BI Desktop?

The script that is used here also works on Power BI Desktop, but there is an easier and little safer way to retrieve data, using the ClockWise Data connector
Information and instructions about the data connector can be found on the Data connector manual