-
Notifications
You must be signed in to change notification settings - Fork 0
/
CmJump.pq
48 lines (35 loc) · 9.8 KB
/
CmJump.pq
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
let
// first call to get access token
// enter your integration key here
SecretKey = "YOUR_INTEGRATION_KEY",
Source = Json.Document(Web.Contents(" https://cloud.hawkindynamics.com/api/token", [Headers=[Authorization="Bearer " & SecretKey]])),
ResponseTable = Table.FromRecords({Source}),
NewTable = Table.TransformColumnTypes(ResponseTable,{{"access_token", type text}, {"token_type", type text}, {"expires_at", Int64.Type}}),
// extract access token from response table
tokenList = NewTable[access_token],
accessToken = Text.From(tokenList{0}),
// Create variable for test type
testType = "7nNduHeM5zETPjHxvm7s",
// second call for CMJ data
Source2 = Json.Document(Web.Contents("https://cloud.hawkindynamics.com/api/dev?" & "testTypeId=" & testType, [Headers=[Authorization="Bearer " & accessToken]])),
responseTable = Table.FromRecords({Source2}),
// expand table
expandTable = Table.ExpandListColumn(responseTable, "data"),
// expand data columns
expandTableData = Table.ExpandRecordColumn(expandTable, "data", {"id", "timestamp", "segment", "testType", "athlete", "Right Avg. Propulsive Force(N)", "Relative Propulsive Net Impulse(N.s/kg)", "Propulsive Phase(s)", "Peak Relative Braking Force(%)", "Positive Impulse(N.s)", "Impulse Ratio", "Countermovement Depth(m)", "Left Force at Peak Propulsive Force(N)", "Jump Height(m)", "Unweighting Phase(s)", "Relative Propulsive Impulse(N.s/kg)", "L|R Avg. Braking RFD(%)", "Peak Relative Propulsive Power(W/kg)", "Stiffness(N/m)", "Avg. Relative Braking Power(W/kg)", "Unweighting Phase %", "Right Force at Peak Braking Force(N)", "RSI", "L|R Avg. Propulsive Force(%)", "Right Avg. Braking Force(N)", "Avg. Relative Propulsive Force(%)", "Left Avg. Landing Force(N)", "L|R Avg. Braking Force(%)", "Peak Propulsive Power(W)", "Force at Min Displacement(N)", "Avg. Braking Power(W)", "Peak Landing Force(N)", "Relative Braking Impulse(N.s/kg)", "Right Avg. Landing Force(N)", "Avg. Propulsive Force(N)", "Flight Time(s)", "Relative Braking Net Impulse(N.s/kg)", "Jump Momentum(kg.m/s)", "Peak Braking Force(N)", "Avg. Braking Velocity(m/s)", "Left Avg. Braking Force(N)", "Time To Takeoff(s)", "Avg. Propulsive Velocity(m/s)", "Takeoff Velocity(m/s)", "L|R Peak Braking Force(%)", "Avg. Propulsive Power(W)", "Right Avg. Braking RFD(N/s)", "L|R Peak Propulsive Force(%)", "Right Force at Peak Propulsive Force(N)", "Avg. Relative Braking Force(%)", "Relative Peak Landing Force(%)", "Peak Relative Braking Power(W/kg)", "mRSI", "System Weight(N)", "Peak Relative Propulsive Force(%)", "Peak Velocity(m/s)", "L|R Avg. Landing Force(%)", "Positive Net Impulse(N.s)", "Landing Stiffness(N/m)", "Relative Force at Min Displacement(%)", "Braking Net Impulse(N.s)", "Braking Phase %", "Left Avg. Braking RFD(N/s)", "Avg. Relative Propulsive Power(W/kg)", "Propulsive Phase %", "Braking Impulse(N.s)", "Right Force at Peak Landing Force(N)", "Propulsive Net Impulse(N.s)", "L|R Peak Landing Force(%)", "Peak Braking Power(W)", "Avg. Braking Force(N)", "Left Avg. Propulsive Force(N)", "Braking Phase(s)", "Avg. Landing Force(N)", "Peak Braking Velocity(m/s)", "Peak Propulsive Force(N)", "Braking RFD(N/s)", "Left Force at Peak Landing Force(N)", "L|R Propulsive Impulse Index(%)", "Left Force at Peak Braking Force(N)", "Propulsive Impulse(N.s)", "L|R Braking Impulse Index(%)", "active", "Time to Stabilization(ms)", "L|R Landing Impulse Index(%)"}, {"id", "timestamp", "segment", "testType", "athlete", "Right Avg. Propulsive Force(N)", "Relative Propulsive Net Impulse(N.s/kg)", "Propulsive Phase(s)", "Peak Relative Braking Force(%)", "Positive Impulse(N.s)", "Impulse Ratio", "Countermovement Depth(m)", "Left Force at Peak Propulsive Force(N)", "Jump Height(m)", "Unweighting Phase(s)", "Relative Propulsive Impulse(N.s/kg)", "L|R Avg. Braking RFD(%)", "Peak Relative Propulsive Power(W/kg)", "Stiffness(N/m)", "Avg. Relative Braking Power(W/kg)", "Unweighting Phase %", "Right Force at Peak Braking Force(N)", "RSI", "L|R Avg. Propulsive Force(%)", "Right Avg. Braking Force(N)", "Avg. Relative Propulsive Force(%)", "Left Avg. Landing Force(N)", "L|R Avg. Braking Force(%)", "Peak Propulsive Power(W)", "Force at Min Displacement(N)", "Avg. Braking Power(W)", "Peak Landing Force(N)", "Relative Braking Impulse(N.s/kg)", "Right Avg. Landing Force(N)", "Avg. Propulsive Force(N)", "Flight Time(s)", "Relative Braking Net Impulse(N.s/kg)", "Jump Momentum(kg.m/s)", "Peak Braking Force(N)", "Avg. Braking Velocity(m/s)", "Left Avg. Braking Force(N)", "Time To Takeoff(s)", "Avg. Propulsive Velocity(m/s)", "Takeoff Velocity(m/s)", "L|R Peak Braking Force(%)", "Avg. Propulsive Power(W)", "Right Avg. Braking RFD(N/s)", "L|R Peak Propulsive Force(%)", "Right Force at Peak Propulsive Force(N)", "Avg. Relative Braking Force(%)", "Relative Peak Landing Force(%)", "Peak Relative Braking Power(W/kg)", "mRSI", "System Weight(N)", "Peak Relative Propulsive Force(%)", "Peak Velocity(m/s)", "L|R Avg. Landing Force(%)", "Positive Net Impulse(N.s)", "Landing Stiffness(N/m)", "Relative Force at Min Displacement(%)", "Braking Net Impulse(N.s)", "Braking Phase %", "Left Avg. Braking RFD(N/s)", "Avg. Relative Propulsive Power(W/kg)", "Propulsive Phase %", "Braking Impulse(N.s)", "Right Force at Peak Landing Force(N)", "Propulsive Net Impulse(N.s)", "L|R Peak Landing Force(%)", "Peak Braking Power(W)", "Avg. Braking Force(N)", "Left Avg. Propulsive Force(N)", "Braking Phase(s)", "Avg. Landing Force(N)", "Peak Braking Velocity(m/s)", "Peak Propulsive Force(N)", "Braking RFD(N/s)", "Left Force at Peak Landing Force(N)", "L|R Propulsive Impulse Index(%)", "Left Force at Peak Braking Force(N)", "Propulsive Impulse(N.s)", "L|R Braking Impulse Index(%)", "active", "Time to Stabilization(ms)", "L|R Landing Impulse Index(%)"}),
// expand test data columns
expandTestData = Table.ExpandRecordColumn(expandTableData, "testType", {"id", "name", "canonicalId"}, {"test.id", "test.name", "test.canonicalId"}),
// expand athlete data columns
expandAthleteData = Table.ExpandRecordColumn(expandTestData, "athlete", {"id", "name", "active", "teams", "groups", "external"}, {"athlete.id", "athlete.name", "athlete.active", "athlete.teams", "athlete.groups", "athlete.external"}),
// extract group data, comma seperated
extractGroups = Table.TransformColumns(expandAthleteData, {"athlete.teams", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
// extract team data, comma seperated
extractTeams = Table.TransformColumns(extractGroups, {"athlete.groups", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
// add date column
addedDate = Table.AddColumn(extractTeams, "date", each DateTimeZone.ToLocal(DateTime.AddZone(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [timestamp]),0))),
// change column formats
changeType = Table.TransformColumnTypes(addedDate,{{"timestamp", Int64.Type}, {"lastSyncTime", Int64.Type}, {"lastTestTime", Int64.Type}, {"count", Int64.Type}, {"date", type date}, {"id", type text}, {"segment", type text}, {"test.id", type text}, {"test.name", type text}, {"test.canonicalId", type text}, {"athlete.id", type text}, {"athlete.name", type text}, {"athlete.teams", type text}, {"athlete.groups", type text}, {"active", type logical}}),
// reorder columns
outputTable = Table.ReorderColumns(changeType,{"id", "active", "timestamp", "segment", "test.id", "test.name", "test.canonicalId", "athlete.id", "athlete.name", "athlete.active", "athlete.teams", "athlete.groups", "athlete.external", "Right Avg. Propulsive Force(N)", "Relative Propulsive Net Impulse(N.s/kg)", "Propulsive Phase(s)", "Peak Relative Braking Force(%)", "Positive Impulse(N.s)", "Impulse Ratio", "Countermovement Depth(m)", "Left Force at Peak Propulsive Force(N)", "Jump Height(m)", "Unweighting Phase(s)", "Relative Propulsive Impulse(N.s/kg)", "L|R Avg. Braking RFD(%)", "Peak Relative Propulsive Power(W/kg)", "Stiffness(N/m)", "Avg. Relative Braking Power(W/kg)", "Unweighting Phase %", "Right Force at Peak Braking Force(N)", "RSI", "L|R Avg. Propulsive Force(%)", "Right Avg. Braking Force(N)", "Avg. Relative Propulsive Force(%)", "Left Avg. Landing Force(N)", "L|R Avg. Braking Force(%)", "Peak Propulsive Power(W)", "Force at Min Displacement(N)", "Avg. Braking Power(W)", "Peak Landing Force(N)", "Relative Braking Impulse(N.s/kg)", "Right Avg. Landing Force(N)", "Avg. Propulsive Force(N)", "Flight Time(s)", "Relative Braking Net Impulse(N.s/kg)", "Jump Momentum(kg.m/s)", "Peak Braking Force(N)", "Avg. Braking Velocity(m/s)", "Left Avg. Braking Force(N)", "Time To Takeoff(s)", "Avg. Propulsive Velocity(m/s)", "Takeoff Velocity(m/s)", "L|R Peak Braking Force(%)", "Avg. Propulsive Power(W)", "Right Avg. Braking RFD(N/s)", "L|R Peak Propulsive Force(%)", "Right Force at Peak Propulsive Force(N)", "Avg. Relative Braking Force(%)", "Relative Peak Landing Force(%)", "Peak Relative Braking Power(W/kg)", "mRSI", "System Weight(N)", "Peak Relative Propulsive Force(%)", "Peak Velocity(m/s)", "L|R Avg. Landing Force(%)", "Positive Net Impulse(N.s)", "Landing Stiffness(N/m)", "Relative Force at Min Displacement(%)", "Braking Net Impulse(N.s)", "Braking Phase %", "Left Avg. Braking RFD(N/s)", "Avg. Relative Propulsive Power(W/kg)", "Propulsive Phase %", "Braking Impulse(N.s)", "Right Force at Peak Landing Force(N)", "Propulsive Net Impulse(N.s)", "L|R Peak Landing Force(%)", "Peak Braking Power(W)", "Avg. Braking Force(N)", "Left Avg. Propulsive Force(N)", "Braking Phase(s)", "Avg. Landing Force(N)", "Peak Braking Velocity(m/s)", "Peak Propulsive Force(N)", "Braking RFD(N/s)", "Left Force at Peak Landing Force(N)", "L|R Propulsive Impulse Index(%)", "Left Force at Peak Braking Force(N)", "Propulsive Impulse(N.s)", "L|R Braking Impulse Index(%)", "Time to Stabilization(ms)", "L|R Landing Impulse Index(%)", "count", "lastSyncTime", "lastTestTime", "date"})
in
outputTable