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.

BradfordFactor.xlsx

Note:

  • Power Query is built into Microsoft Excel 2016 by default. If you have an earlier version, you will first need to set the add-in up. For more information, see: Setting Up Power Query.
Setting up the Query
  1. Use the following link to download the query: Bradford Factor.
  2. Open the file and click on one of the cells in the table.
1. Setting up the Query
  1. Select the Query tab.
  2. Click Refresh.
  1. From the pop-up, select Basic.
  2. Enter your Cezanne Username and Password and select the URL indicated below:
  3. Click Connect.
  4. The query will refresh with your data. To configure the parameters, please see Section 2 below.
Configuring the Query Parameters

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:

  1. Click on one of the cells in the table.
Length of a Working Day
  1. Select the Query tab.
  2. Click Edit.
  1. Click Advanced Editor
  1. Change 7.5 to the amount of hours in your organisation's full working day.
  2. Click Done.
  1. 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:

  1. Select the "Params" sheet.
Reference Dates
  1. Change the Start and End Date that you want to run the query for.
  1. Select "Sheet2",
  1. Select the Query tab.
  2. Click Refresh.

 

Running the Query

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.