GraphQL to Power Query/BI - M-code Pagination

Anonymous — Tue, 30 Jul 2024 12:16:37 GMT

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?

Re: GraphQL to Power Query/BI - M-code Pagination

Anonymous — Wed, 31 Jul 2024 09:37:07 GMT

Hi @Anonymous ,

This error typically occurs when the JSON data being imported has extra characters or formatting issues. The following thread is the one which has the similar problem as yours, hope it can help you.

Solved: Data Format .Error: We found extra characters at t... - Microsoft Fabric Community

It is responding I have checked through web.browsercontains . Prolem has solved by using header and bearer token in it. thanks for the reply.

Please review the applied codes to see from which step the error is reported?

Update the codes for content part as below and check if it can work:

Content = Text.ToBinary(Json.FromValue(body))

Best Regards

Re: GraphQL to Power Query/BI - M-code Pagination

Anonymous — Tue, 06 Aug 2024 08:24:43 GMT

Hi @Anonymous ,

Has your problem been resolved? If so, could you kindly mark your post as Answered? Additionally,could you please kindly share your solution if it’s convenient. This will greatly assist others in the community who may be facing similar challenges. Thank you for your dedication.

Best Regards

topic GraphQL to Power Query/BI - M-code Pagination in Developer

GraphQL to Power Query/BI - M-code Pagination

Re: GraphQL to Power Query/BI - M-code Pagination

Re: GraphQL to Power Query/BI - M-code Pagination