Outdated – Loading GraphQL data (monday.com API v2) into PowerBI

outdated-monday-GraphQL-PowerBI.m

// Playing around with a PowerBI query to pull data from monday.com into PowerBI
// This can come in handy when building PowerBI reports that utilize GraphQL endpoints for loading data.

let
    Source = Web.Contents(
	"https://api.monday.com/v2",
	[
		Headers=[
			#"Method"="POST",
			#"Content-Type"="application/json",
			#"Authorization"="<your_personal_token_here>"
		],
    // Notice the quote escaping here
		Content=Text.ToBinary("{""query"": ""{ boards (ids: <your board ID here>) { items { name id column_values (ids:["<your column id here>"] { id title value } } } }""}")
	]
    ),
    #"JSON" = Json.Document(Source)
in
    #"JSON"

Hey, @yuhgto thanks for providing the code. I'm getting a 500 error when I try to use it on PBI. Any thoughts? Token and board Id looks fine.

Hi @yuhgto how do we address the error mentioned in the previous comment?

Hi @paulosoaresf, @yuhgto
500 errors occur from Monday if query have error.
If you're using the ids:[""], GraphQL require the Column name to be surounded by double-quotes,
In order to amend double quotes in the M query, you have to use ""
Hence, the ids looks like ids:["""", """"]

Hey folks – I'm marking this as outdated as it does not use our updated cursor pagination. I'm not a deep PowerBI expert, so I am not 100% sure how to implement it in PowerBI.

If anyone wants to fork this and connect it to the new items_page object, be my guest! An explanation about cursor pagination is here.

@yuhgto I used your query as a starting point to work through building queries using the cursor pagination. For anyone interested, have a look here https://gist.github.com/hmbouk/b5712f8d4639eb85328bf4828b402284 and here https://community.monday.com/t/using-power-bi-to-retrieve-boards-using-cursor-based-pagination/70535.

My approach has limitations and maybe someone can improve on what I came up with.

@yuhgto I used your query as a starting point to work through building queries using the cursor pagination. For anyone interested, have a look here https://gist.github.com/hmbouk/b5712f8d4639eb85328bf4828b402284 and here https://community.monday.com/t/using-power-bi-to-retrieve-boards-using-cursor-based-pagination/70535.

My approach has limitations and maybe someone can improve on what I came up with.

Dear hmbouk, on your approach is possible to bring also the group of each item?

@yuhgto I used your query as a starting point to work through building queries using the cursor pagination. For anyone interested, have a look here https://gist.github.com/hmbouk/b5712f8d4639eb85328bf4828b402284 and here https://community.monday.com/t/using-power-bi-to-retrieve-boards-using-cursor-based-pagination/70535.
My approach has limitations and maybe someone can improve on what I came up with.

Dear hmbouk, on your approach is possible to bring also the group of each item?

@jscortesgh Unfortunately, when I added the group to the query, the data returned couldn't be transformed into a set of rows with the group as a new column. What I tested and found worked well was to duplicate the Get_1stPage and Get_RemainingPages. I called them Get_1stPageGroup and Get_RemainingPagesGroup. Then I modified the query to look like this -> MondayQuery3 = ") {items_page(limit: 100) {cursor items {id group {title} }}}}""}",

Also, duplicate the Monday board query so this one calls the Get_1StPageGroup and Get_RemainingPagesGroup queries.

This will create a table with your board columns and another table with your groups for each record. Then in Power Query you can select Merge Queries (I chose to Merge as new) to merge the two tables into a single table. You can join the two tables using the ID column for the data. This will give you a nice clean table with group vales.