Hi - over several weeks I have tried to update the 2 lovely all-purpose queries I have been using for all of my PowerBI reports against monday.com. I am now pulling the last of my hairs out… I am not an expert query writer and am just getting frustrated now! I had 1 query for all items in a selected board and 1 query for all sub-items (and then related them in Power BI). I just need all-purpose queries that will handle 500-1000 items, give me the contents of the subitem column and give me MirrorValue and BoardRelationValue data… I need the whole query, not just the GraphQL - so that I can drop into a Power BI dataflow.
THIS WAS MY ITEMS QUERY…
let
Key = “”,
Board = “”,
Source = Web.Contents(
“https://api.monday.com/v2”,
[
Headers=[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“Bearer " & Key
],
Content=Text.ToBinary(”{““query””: ““query { boards(ids: " & Board & “) { items { id, name, updated_at, group { title }, columns: column_values { title, text } } } }””}”)
]
),
Data = Table.FromList(Json.Document(Source)[data][boards]{0}[items], Record.FieldValues, {“ID”, “Title”, “UpdatedAt”, “Group”, “Columns”}),
#“Monday” = Table.FromRecords(Table.TransformRows(Data, each
List.Accumulate([Columns], [
ID = [ID],
Title = [Title],
UpdateDate = [UpdatedAt],
Group = [Group][title]
], (state, current) => Record.AddField(state, current[title], current[text]) )
))
in
#“Monday”
THIS WAS MY SUBITEMS QUERY…
let
Key = “”,
Board = “”,
Source = Web.Contents(
“https://api.monday.com/v2”,
[
Headers=[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“Bearer " & Key
],
Content=Text.ToBinary(”{““query””: ““query { boards(ids: " & Board & “) { items {parent_item {id}, id, name, updated_at, group { title }, columns: column_values { title, text } } } }””}”)
]
),
Data = Table.FromList(Json.Document(Source)[data][boards]{0}[items], Record.FieldValues, { “ParentItem”, “ID”, “Title”, “UpdatedAt”, “Group”, “Columns”}),
Monday = Table.FromRecords(Table.TransformRows(Data, each
List.Accumulate([Columns], [
Parent = [ParentItem],
ID = [ID],
Title = [Title],
UpdateDate = [UpdatedAt],
Group = [Group][title]
], (state, current) => Record.AddField(state, current[title], current[text]) )
))
in
#“Monday”