Hi ,
Im quite new to Power BI and GraphQL espesially. Im using chatgpt to fidn the answers. I want to fetch data from a ERP to Power BI via their Graph QL API. The info follows below and the questions is at the end 🙂 Helpful for your support!
Graph QL code::
query MyQuery {
accounts(first: 10) {
edges {
node {
code
description
}
cursor
}
pageInfo {
hasNextPage
}
}
}
Response in JSON:
{
"data": {
"accounts": {
"edges": [
{
"node": {
"code": "1045",
"description": "XYZ"
},
"cursor": "92627"
}
],
"pageInfo": {
"hasNextPage": true
}
}
}
}
Chat GPT suggests M-code::
let
// Define the base URL for the API
baseUrl = "https://system.net",
// Define the authorization token
authToken = "ASYYJjCLOeBxaS4768obS",
// Function to fetch a page of data
fetchPage = (optional afterCursor as nullable text) as record =>
let
query = if afterCursor = null then
"query MyQuery { accounts(first: 10) { edges { node { code description } cursor } pageInfo { hasNextPage } } }"
else
"query MyQuery { accounts(first: 10, after: """ & afterCursor & """) { edges { node { code description } cursor } pageInfo { hasNextPage } } }",
body = "{ ""query"": """ & query & """ }",
options = [
Headers = [
#"Content-Type" = "application/json",
#"Authorization" = "Bearer " & authToken
],
Content = Text.ToBinary(body)
],
response = Json.Document(Web.Contents(baseUrl, options)),
accounts = response[data][accounts]
in
accounts,
// Function to fetch all pages
fetchAllPages = (initialPage as record) as list =>
let
fetchPageRecursive = (page as record, acc as list) as list =>
let
newAcc = List.Combine({ acc, page[edges] }),
nextPage = if page[pageInfo][hasNextPage] then @fetchPageRecursive(fetchPage(List.Last(page[edges])[cursor]), newAcc) else newAcc
in
nextPage
in
fetchPageRecursive(initialPage, {}),
// Fetch the first page
firstPage = fetchPage(null),
// Fetch all pages
allPages = fetchAllPages(firstPage),
// Convert the data to a table
dataTable = Table.FromList(allPages, Splitter.SplitByNothing(), {"Data"}),
expandedTable = Table.ExpandRecordColumn(dataTable, "Data", {"node"}, {"node"}),
finalTable = Table.ExpandRecordColumn(expandedTable, "node", {"code", "description"}, {"code", "description"})
in
finalTable
The result i get in Power Query is
"DataFormat.Error: We found extra characters at the end of the JSON input.
Details:
Value=<
Position=4"
The question is
1. Is it possible to automatically paginate all the data fromt he graphql API with M-code?
2. Would it be better to fetch the data differently to make it easier as im a beginner?
3. Do you see what is wrong with the Chat GPT suggestion and how to handle it?
