Hawkin Dynamics
Power BI & Excel

Power Query Templates

Connect Microsoft Power BI and Excel to the Hawkin Dynamics API using Power Query (M language). Pre-built templates, custom Power BI scripts, and standalone scripts for every workflow.

Version
1.0
Platform
Power BI & Excel
License
MIT

Overview

Three approaches to connecting Power BI and Excel to the Hawkin Dynamics API.

Template Files Power BI Scripts Generic Scripts
Audience Anyone — ready-to-go dashboard Power users building custom reports Developers & Excel users
Platform Power BI Power BI Power BI & Excel
Setup Open .pbit, enter credentials One-time parameter + function setup Copy-paste per script
Schema Dynamic (automatic) Dynamic (automatic) Dynamic (automatic)
Pagination Server-side cursor Server-side cursor Server-side cursor

Installation

Download the Power BI template or individual scripts from the Downloads tab.

1

Power BI Users

Download the Power BI Template (.pbit) for the fastest setup, or the Power BI Scripts for full customization.

Download Power BI Template (.pbit)
2

Excel Users

Download the Excel Template (.xltx) with preloaded queries, or the Generic Scripts to paste individually into Excel's Power Query editor.

Download Excel Template (.xltx)

Quick Start

Get up and running with the Power BI template in four steps.

1

Download the Template

Download the template from the Downloads tab, or use the direct link below.

Download Power BI Template (.pbit)
2

Open in Power BI Desktop

Double-click the .pbit file to open it in Power BI Desktop.

3

Enter Your Parameters

When prompted, enter the required values:

  • API Integration Key — your Integration Key from Hawkin Settings → Integrations
  • API Region — your region: Americas, Europe, or Asia/Pacific
  • Organization Name (optional) — Organization ID, leave blank if not applicable

The optional filters can stay blank/default for an all-tests pull:

  • from (Date, optional) — earliest test date
  • to (Date, optional) — latest test date
  • Include Equipment ID (default false) — include hardware equipment ID per test
  • Include Inactive Tests (default false) — include disabled trials
  • Include Inactive Athletes (default false) — include disabled athletes
4

Load Data

Click Load — Power BI builds the data model and populates all tables.

Regional Endpoints

Set the API Region parameter to match your organization's data residency.

Region Value Base URL
Americas "Americas" cloud.hawkindynamics.com
Europe "Europe" eu.cloud.hawkindynamics.com
Asia-Pacific "Asia/Pacific" apac.cloud.hawkindynamics.com
Tutorials & Guides

User Guide

Detailed walkthroughs for each Power Query approach — from zero-code templates to fully custom scripts.

Template Files (Recommended)

The fastest way to get started. A pre-built .pbit file with all queries, functions, and table relationships pre-configured.

Features

  • Zero coding — no Power Query editing needed
  • Instant structure — tables for Athletes, Teams, Groups, Tags, Test Types, and all 11 test types pre-linked
  • Dynamic schema — new API metrics appear automatically
  • Server-side cursor pagination — handles arbitrarily large datasets without manual chunking
Download Power BI Template (.pbit)

Architecture

Parameters Required: API Integration Key, API Region Optional: Organization Name, from, to, Include Equipment ID, Include Inactive Tests, Include Inactive Athletes | v Auth query -> funcAuth() -> access token (cached) | v Data queries (CMJ, SquatJump, Athletes, etc.) | | v v funcTests() funcAthletes() / funcTeams() / funcTags() | (cursor-paginated until nextCursor is null) v Dynamic schema expansion -> typed & reordered output table

Power BI Scripts

For power users who want full control over their report layout. Uses the same modular architecture as the Template Files, but you build your own report from scratch.

Download Power BI Scripts (.zip)
1

Create Parameters

In Power BI Desktop: Home > Transform Data > Manage Parameters. Create the following parameters (names must match exactly):

  • API Integration Key (Text)
  • API Region (Text) — Americas, Europe, or Asia/Pacific
  • Organization Name (Text, optional)
  • from (Date, optional)
  • to (Date, optional)
  • Include Equipment ID (Logical, default FALSE)
  • Include Inactive Tests (Logical, default FALSE)
  • Include Inactive Athletes (Logical, default FALSE)
2

Import Functions

For each file in the functions/ folder: Home > Get Data > Blank Query > Advanced Editor, paste the .pq code, rename to match exactly (funcAuth, funcTests, epochNow, etc.).

3

Import Auth Query

Import Auth.pq as a query named Auth.

4

Import Data Queries

For each table needed (CMJ.pq, Athletes.pq, etc.), create a blank query, paste code, and rename.

Tip: Function query names must match exactly (e.g., funcAuth, funcTests, epochNow) — data queries reference them by name.
Video Tutorial: Watch the setup tutorial: https://www.loom.com/share/113272339e424218b5dd5ccebd245c33

Generic Scripts (Standalone)

Self-contained scripts where all configuration is at the top. Each handles its own authentication. Works in both Power BI and Excel.

Download Generic Scripts (.zip)

Configuration Section

// User Configuration SecretKey = "YOUR_INTEGRATION_KEY", orgName = null, reg = "Americas", fromDate = "", // Unix timestamp; empty = no lower bound toDate = "", // Unix timestamp; empty = no upper bound includeInactive = false, // true to include disabled records includeEid = false, // (test scripts) true to include equipment ID

Steps

1

Open the Script

Open the desired .pq file from the Generics/ folder.

2

Update Configuration

Update the configuration variables at the top of the script with your credentials and region.

3

Copy the Script

Copy the entire script contents.

4

Paste into Advanced Editor

Power BI: Home > Get Data > Blank Query > Advanced Editor. Excel: Data > Get Data > Blank Query > Advanced Editor.

5

Execute

Click Done to execute the query.

Note: Generic scripts authenticate independently per query (no shared token cache). They use server-side cursor pagination internally, so all-tests pulls work without manual chunking.

Using with Excel

Only the Generic scripts work in Excel's Power Query editor (the Power BI Scripts approach requires Power BI-specific features like Parameters).

For a zero-code experience, download the Excel Template with preloaded queries:

Download Excel Template (.xltx)

Or use the Generic scripts to paste individual queries into Excel's Power Query editor:

Download Generic Scripts (.zip)
1

Open Power Query in Excel

Open Excel → Data → Get Data → From Other Sources → Blank Query.

2

Open Advanced Editor

In the Power Query Editor, click Advanced Editor.

3

Paste Script

Paste the Generic script contents into the editor.

4

Update Credentials

Update the configuration variables at the top with your Integration Key, region, and date range.

5

Load Data

Click Done, then Close & Load to import the data into your worksheet.

Dynamic Schema Handling

All test-type scripts use dynamic field discovery — new metrics added to the API appear as columns automatically without script updates.

Pattern

allFields = List.Distinct( List.Combine( List.Transform( expandTable[data], each if _ is record then Record.FieldNames(_) else {} ) ) ), expandData = Table.ExpandRecordColumn( expandTable, "data", allFields, allFields )

Metadata columns are typed explicitly. Metric columns are auto-typed as number. Columns are reordered: metadata first, metrics alphabetically, summary fields last.

Supported Test Types

Test Type Generic Script Template Query Test Type ID
Countermovement Jump CmJump.pq CMJ.pq 7nNduHeM5zETPjHxvm7s
CMJ Rebound CmJumpRebound.pq CMJ_Rebound.pq gyBETpRXpdr63Ab2E0V8
Squat Jump SquatJump.pq SquatJump.pq QSy5LM7CZQJ1MZE37lMO
Drop Jump DropJump.pq DropJump.pq 5pRSUQVSJVnxijpPMck3
Drop Landing DropLanding.pq DropLanding.pq umnEZPgi6gG9QwMvGSig
Isometric Isometric.pq Isometric.pq ubeWMPN1lJFbuQbEqc1C
Multi Rebound MultiReb.pq MultiReb.pq r4fhrkPdYlLxYQxEeM78
Free Run FreeRun.pq FreeRun.pq pqgf2TPUOQOQs6r0HQWb
Weigh In WeighIn.pq WeighIn.pq uSvrZaHqDvfZn7S5KnHt
TS Free TS_Free.pq TS_Free.pq 4KlQgKmBxbOY6uKTLDFL
TS Isometric TS_Isometric.pq TS_Isometric.pq wGBM3RRHTBVT8GhAOi0Z
Version History

Changelog

Release notes and version history for Power Query templates.

May 2026
v1.0.20260527
Fixed

Corrects lastTestedOn unit handling (seconds, not ms)

  • Derived lastTestedOnDate column now produces correct calendar dates — previously collapsed to ~1970 because the value was treated as milliseconds when the API actually returns seconds
  • funcAthletes and Generics/Athletes.pq: removed / 1000 from the #duration(0, 0, 0, [lastTestedOn]) call
  • Raw lastTestedOn numeric column was always correct — only the derived date column was affected
  • Non-breaking: no function signatures or PBI parameters changed; drop-in replacement for v1.0.20260519
May 2026
v1.0.20260519
Added Updated

Athlete profile fields & ForceTime modernization

  • New optional Athlete columns when populated: image, position, dob, sport, height (cm), lastTestedOn (epoch s), and derived lastTestedOnDate
  • funcAthletes uses graceful field detection — columns only appear when at least one athlete has the field populated
  • ForceTime data-query rewritten to delegate to new funcForceTime via the shared funcAuth token flow
  • New PBI parameter Test ID (Text) drives the ForceTime query — no more editing query bodies to switch tests
  • Athletes table drops the envelope count column from per-athlete output
  • Non-breaking: no function signatures or PBI parameters renamed
Apr 2026
v1.0.20260427
Added Updated Breaking

API v1.13+ modernization — server pagination, new optional parameters

  • Cursor-based server pagination inside funcTests — client-side 30-day chunking removed
  • New optional parameters: from, to, Include Equipment ID, Include Inactive Tests, Include Inactive Athletes
  • Renamed PBI parameters for readability: SecretKey → API Integration Key, reg → API Region, orgName → Organization Name
  • Removed Organization Start Date parameter
  • funcTests signature changed (drops sync, makes fromDate/toDate optional, adds includeInactive/includeEid)
  • funcAthletes signature gained optional includeInactive flag
  • Generics scripts paginate internally and gain includeInactive/includeEid user-config knobs
Mar 2026
v1.0.20260323
Updated Added

Dynamic schema, function consolidation, folder restructure

  • Dynamic schema handling — new API metrics appear automatically as columns
  • Consolidated 12 individual test-type functions into single funcTests.pq
  • Added test tag extraction (tag IDs and names)
  • Added athlete.external field support
  • Renamed Parameterized/ → Parameterized Template/, Template File/ → Template Files/
  • Updated Power BI template to v1.20260323
Jan 2026
v1.0.20260109
Added

Initial structured release

  • Added Power BI Template (.pbit) — zero-code setup
  • Created Parameterized directory with function-based queries
  • Reorganized repository from flat structure to categorized folders
  • Added comprehensive README documentation
Downloads

Download Templates & Scripts

Pre-built Power BI templates, Excel templates, and Power Query scripts ready to connect to the Hawkin Dynamics API.

Power BI Template
Recommended
187 KB · .pbit
The fastest way to get started. Open this template in Power BI Desktop, enter your Integration Key and region, and all tables are built automatically. Includes all 11 test types, athlete roster, teams, groups, tags, and test type metadata — pre-linked in the data model.
  • hawkin-connect-pbi-template-v1.20260527.pbit — Power BI Template file
Download Power BI Template (.pbit)
Power BI Scripts
208 KB · .zip
For power users building custom Power BI reports. Contains reusable Power Query functions and lightweight data queries that inherit credentials from Power BI Parameters. Uses server-side cursor pagination (no manual chunking) and dynamic schema handling.
  • functions/ — 6 reusable functions (funcAuth, funcTests, funcAthletes, funcTeams, funcTags, epochNow)
  • data-queries/ — 18 data table queries (Athletes, Teams, Groups, Tags, TestTypes, ForceTime, 11 test types, Auth)
  • hawkin-connect-pbi-template-v1.20260527.pbit — Matching Power BI template
Download Power BI Scripts (.zip)
Generic Scripts (Standalone)
30 KB · .zip
Self-contained Power Query scripts that work in both Power BI and Excel. Each script handles its own authentication — paste one into the Advanced Editor, update the config at the top, and run. Ideal for developers, debugging, or Excel users.
  • Athletes.pq, Teams.pq, Groups.pq, Tags.pq, TestTypes.pq, ForceTime.pq
  • Tests by Test Type/ — 11 test-type scripts (CmJump, SquatJump, DropJump, Isometric, etc.)
Download Generic Scripts (.zip)
Excel Template
1.1 MB · .xltx
A Microsoft Excel template with preloaded Power Query scripts. Open in Excel, enter your Integration Key in the configuration, and refresh to pull data directly into worksheets. Same queries as the Power BI template, optimized for Excel.
  • hawkin-connect-excel-template-v1.20260527.xltx — Excel Template file
Download Excel Template (.xltx)
Release Notes
100 KB · .pdf
Detailed release notes for the latest version (v1.0.20260527), a non-breaking bug-fix release that corrects lastTestedOn unit handling (seconds, not milliseconds) in funcAthletes on top of the v1.0.20260519 feature release.
Download Release Notes (.pdf)