Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

GraphQL to Power Query/BI - M-code Pagination

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?

 

 

 

2 REPLIES 2
Anonymous
Not applicable

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

Anonymous
Not applicable

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?

vyiruanmsft_0-1722418557364.png

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

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

Best Regards

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.