Have you ever tried making a web request to
I had a short deadline, and the solution I found was using M Language.
In Power Query:
Open a Blank query > right click > Advanced editor.
Try the script below:
let
url = "https://api.pipefy.com/graphql",
token = "Bearer YOUR_TOKEN_HERE",
// Function to make the GraphQL request
FetchPage = (cursor as nullable text) as record =>
let
queryBody = if cursor = null then
"{ ""query"": ""query { allCards(pipeId: \""PIPE_ID_HERE\"", first: 40) { nodes { title updated_at due_date current_phase { name } fields { name value } } pageInfo { hasNextPage endCursor } } }"" }"
else
"{ ""query"": ""query { allCards(pipeId: \""PIPE_ID_HERE\"", first: 40, after: \""" & cursor & "\"") { nodes { title updated_at due_date current_phase { name } fields { name value } } pageInfo { hasNextPage endCursor } } }"" }",
response = Web.Contents(
url,
[
Headers = [
#"Authorization" = token,
#"Content-Type" = "application/json"
],
Content = Text.ToBinary(queryBody)
]
),
jsonResponse = Json.Document(response),
data = jsonResponse[data][allCards]
in
data,
// Recursive function to iterate through all pages
GetAllPages = (cursor as nullable text, accumulatedCards as list) as list =>
let
currentPage = FetchPage(cursor),
currentCards = List.Transform(currentPage[nodes], each
[
title = _[title],
updated_at = _[updated_at],
due_date = _[due_date],
current_phase = _[current_phase][name],
fields = _[fields]
]
),
newCards = List.Combine({accumulatedCards, currentCards}),
hasNext = currentPage[pageInfo][hasNextPage],
nextCursor = currentPage[pageInfo][endCursor]
in
if hasNext then
@GetAllPages(nextCursor, newCards)
else
newCards,
// Calling GetAllPages function to retrieve all pages
allCards = GetAllPages(null, {}),
// Converting the list of records into a table
resultTable = Table.FromRecords(allCards),
// Extracting unique custom field names
allFieldNames = List.Distinct(List.Combine(List.Transform(resultTable[fields], each List.Transform(_, each _[name])))),
// Adding columns for each custom field
resultWithFields = Table.TransformColumns(resultTable, {"fields", each
let
// Create a record for each field
fieldRecord = Record.FromList(
List.Transform(allFieldNames, (fieldName) =>
let
// Attempt to find the field value
selectedField = List.First(List.Select(_, (field) => field[name] = fieldName), null)
in
if selectedField <> null then selectedField[value] else null // Retorna o valor ou null
),
allFieldNames
)
in
fieldRecord
}),
// Expanding custom fields into separate columns
finalTable = Table.ExpandRecordColumn(resultWithFields, "fields", allFieldNames)
in
finalTable
Considerations:
FetchPage
Makes the HTTP request to the API; uses pagination with the cursor to fetch data in parts (40 records per page).
cursor as nullable text
Used to handle pagination, indicating the position of the next page to be fetched.
first: 40
Defines the number of records retrieved per request (you can adjust this value as needed).
→ I chose 40 because it worked best for my request size and Pipe structure. Check out .
→ The documentation allows you to build your GraphQL queries by selecting the exact information you need. Once you have your query ready, you can ask gepeto to convert it to JSON format for you.
Final Result
The script converts all the data into a neat table in Power BI (you can expand it as needed, and the code will dynamically adjust).
SOCIAL SHARE CARD GENERATOR