Skip to main content

Getting Started

Description​

The KWS Timesheet Powerquery (PQ) is a file that consumes data from the below files

  • POP Timesheet
  • Week Timesheet
  • Roster Tool

The PQ is designed to read all the information within these files without the need to open them. This also means that it’s possible to extract information from 10’s or 100’s of files at one time.

The PQ operates in four stages

  1. OrderList: The PQ file reads the directory or location of all the files to be consumed.
  2. EXT Data Pull: The PQ reads all the data inside the files and stores it temporarily in computer memory
  3. INT Data Pull: The PQ makes a local copy of the EXT Data pull inside the excel file. This means the directory can be relocated without breaking a link to the data.
  4. Manipulation: The PQ formats the data in the required outputs for MRMS and TRACK.

Setup​

To set up the document, save all your files into a folder and then copy the folder path and paste it into the PQ filepath cell.

Click dropdown box to select which type of files are in the folder (Weekly, POP, Roster)

Powerquery1

Click the refresh buttons in the order of 1, 2, 3, 4. Be sure to wait for each refresh to finish before moving on to the next. This can be identified as in progress by “Running background query” displayed in the bottom left corner.

Powerquery2

Data Tab​

All the mapping data from the PQ can be found in the Data tab. This is all user serviceable information that Monadelphous can update.

This includes:

  • Contract specific Internal and External Position Mapping
  • Track Personnel Labour ID’s
  • Track RTIO approved reason’s for POP hour variances
  • Roster schedule day codes
  • Track Location (FIFO/Local) Codes
  • Track Contract Codes
  • Track Asset Id’s
  • Timesheet Timecode overrides

FAQ​

Q: The Powerquery has been loading for 10 minutes and hasn’t refreshed?

A: Occasionally, the powerquery may get stuck for a number of reasons. Be sure to check the following:

  1. That no files within the filepath folder are open in excel.
  2. That only one PQ file is open

If the above does not fix the problem, close and reopen the PQ file and try again.