How do I rewrite my broken query?
I’m writing some M code in Power Query that has a GraphQL portion submitted to an API. The goal is to retrieve some employee records: Id, Name, & AccessStatus. Right now I’m receiving a Bad Request error, supposedly due to my syntax being incorrect at some point in the query. Thus far, all the queries I’ve been using are based off a template query for a different node. The root node is shown in pic #1, and I’m looking to query WorkerAccess (its doc is in pic #2). The rest of the branch up to the node I’m interested in is shown in pic #3.
Of the four queries I’ve created based on the template, the only changes needed for my purposes were minor. I’ve included the template below, with comments next to the interchangeable terms. As far as I know, I don’t need to change anything else to get the results I need. However, the WorkerAccess query has a difference in the args from the other queries I’ve used, which is that it seems to take a worker Id. This is one possible reason for the Bad Request error I’m getting whenever I try to invoke. The other queries usually have ‘First’ and ‘After’ included in their args.
let
Source = (apiToken as text, optional endCursor as text, optional data as list) =>
let
endCursor = if endCursor is null then "" else endCursor,
query = "{
""query"": ""
{
// Everything after 'results:' can be changed depending on the query
results: PeopleOnSiteHistory(First: 2, After: """ & endCursor &""") {
PageInfo {
HasNextPage
EndCursor
}
Items {
PersonId // these items can be changed to reflect what I want back
PersonName
}
}
}
""
}",
JSON = Web.Contents("https://cloud.3dsafety.com.au/graphql",
[
Headers = [#"X-API-Key"=apiToken, #"Content-Type"="application/json"],
Content = Text.ToBinary(query)
]
),
Source = Json.Document(JSON),
pageInfo = Source[data][results][PageInfo],
items = Source[data][results][Items],
appendedData =
if pageInfo[HasNextPage] = true and data is null then
List.Combine({{}, items})
else List.Combine({data, items}),
output =
if pageInfo[HasNextPage] = true then
// Name of PQ query goes here after '@' sign
@GetPeople(apiToken, pageInfo[EndCursor], appendedData)
else
// Items go in the curly braces again
Table.FromList(appendedData, Record.FieldValues, {"PersonId", "PersonName"})
in
output
in
Source
This is the broken query, with the relevant changes shown:
... "{
""query"": ""
{
results: WorkerAccess(Id: 1) {
PageInfo {
HasNextPage
EndCursor
}
Items {
Id
Name
AccessStatus
}
}
}
""
}",
...
@GetWorkerStatus(apiToken, pageInfo[EndCursor], appendedData)
else
Table.FromList(appendedData, Record.FieldValues, {"Id", "Name", "AccessStatus"})
...
