Power Query Example: Bradford Factor between two specific dates
Power Query is an add-in which enables you to access your Cezanne data within Microsoft Excel using the API.
This article explains how to use the Power Query add-in to find an employee's Bradford Factor between two specified dates.
- Use the following link to download the query: Bradford Factor.
- Open the file and click on one of the cells in the table.
- Select the Query tab.
- Click Refresh.
- From the pop-up, select Basic.
- Enter your Cezanne Username and Password and select the URL indicated below:
- Click Connect.
- The query will refresh with your data. To configure the parameters, please see Section 2 below.
In order to run the query correctly, you need to set up the following parameters:
Length of a Working Day
By default, the query is set up to calculate a full working day as 7.5 hours. If your organisation uses a different value for the amount of hours in a full working day (e.g. 8 hours), you will need to change this in the query settings:
- Click on one of the cells in the table.
- Select the Query tab.
- Click Edit.
- Click Advanced Editor
- Change 7.5 to the amount of hours in your organisation's full working day.
- Click Done.
- Select Close & Load.
Reference Dates
The query contains two worksheets, the "Params" sheet and the data sheet.
The "Params" sheet contains Start Date and End Date. To change these and run the query again:
- Select the "Params" sheet.
- Change the Start and End Date that you want to run the query for.
- Select "Sheet2",
- Select the Query tab.
- Click Refresh.
Once you have set up the query and configured the parameters, you can save the excel file on your computer. Then whenever you want to run the query, open the file, set the Reference Dates and refresh the query.
To export this or send this information to other people in your organisation, copy and paste the data from Sheet2 into a new Excel Workbook and send the new file to them. You could use the Documents & E-mails functionality in Cezanne to Upload, Track and optionally request employees to sign that they have read the data from the query.
For more information, see: Documents & E-mails.