-
-
Save yuhgto/9d2d69aa1038954c1b8e0488cca290c2 to your computer and use it in GitHub Desktop.
| // 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" |
@jjtagh
Yes, I believe that is due to the escape chars. Try to log and run your final query in monday playground
@firewall004 The query runs in the playground, but it seems I have to put it all onto one line as M code, or else it throws an error.
Has anyone got this to run? I get several errors as the code does not seem to compile correctly. I've put it all on one line, but it's still not working for me.
Where do you run this?
@SusieN246 this code goes into the Power Query editor in Excel, or the Transform Data button in Power BI. It is the same tool for both platforms. You can even develop your queries in Excel and paste them into Power BI.
@gcam032 The all in one line I was referring to is just for the GraphQL, ie the Content = Text.ToBinary statement on line 14. So,
1: Get your GraphQL working in the Monday Playground
2: Put that statement all one line, and include in the API call
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.
Thanks for this template Dipro! I notice that we can't prettify the query code at all or it breaks in Power Query. I didn't think Power Query/M cares much about white space. Are you able to explain why it breaks? Is it something to do with all the escaping?