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, parameterized functions, 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	Parameterized Template	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)
Chunking	30-day auto-chunking	30-day auto-chunking	Single API call

Installation

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

Power BI Users

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

Download Power BI Template (.pbit)

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.

Download the Template

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

Download Power BI Template (.pbit)

Open in Power BI Desktop

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

Enter Your Parameters

When prompted, enter the following:

SecretKey — your Integration Key from Hawkin Settings → Integrations
reg — your region: Americas, Europe, or Asia/Pacific
orgName — Organization ID (leave blank if not applicable)
Organization Start Date — earliest date to pull test data

Load Data

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

Regional Endpoints

Set the reg 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
30-day chunking — handles large date ranges without timeouts

Download Power BI Template (.pbit)

Architecture

Parameters (SecretKey, reg, orgName, Organization Start Date) | v Auth query -> funcAuth() -> access token (cached) | v Data queries (CMJ, SquatJump, Athletes, etc.) | | v v funcTests() funcAthletes() / funcTeams() / funcTags() | v Dynamic schema expansion -> typed & reordered output table

Parameterized Template

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 Parameterized Scripts (.zip)

Create Parameters

In Power BI Desktop: Home > Transform Data > Manage Parameters. Create the following parameters:

SecretKey (Text)
reg (Text)
orgName (Text)
Organization Start Date (Date)

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.).

Import Auth Query

Import Auth.pq as a query named Auth.

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", startDate = "", endDate = "",

Steps

Open the Script

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

Update Configuration

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

Copy the Script

Copy the entire script contents.

Paste into Advanced Editor

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

Execute

Click Done to execute the query.

Warning: Generic scripts authenticate independently per query and use a single API call (no chunking). For large date ranges, use the Parameterized Template approach.

Using with Excel

Only the Generic scripts work in Excel's Power Query editor (the Parameterized Template 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)

Open Power Query in Excel

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

Open Advanced Editor

In the Power Query Editor, click Advanced Editor.

Paste Script

Paste the Generic script contents into the editor.

Update Credentials

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

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.

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-api-template-v1.20260406.pbit — Power BI Template file

Download Power BI Template (.pbit)

Parameterized Template Scripts

198 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. Includes automatic 30-day date 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-api-template-v1.20260406.pbit — Matching Power BI template

Download Parameterized 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_api_template_v20260406.xltx — Excel Template file

Download Excel Template (.xltx)

Release Notes

83 KB · .pdf

Detailed release notes for the latest version (v1.0.20260323), including dynamic schema changes, function consolidation, and upgrade instructions.

Download Release Notes (.pdf)