Using Power BI to retrieve boards using Cursor-based pagination

monday Apps & Developers
, , ,
1

This is a follow-up to a post (Connecting Power BI to Monday.com API 2023-10) about using Power BI to call the 2023-10 API. I have a set of Power BI M language functions and query that can retrieve data from a board using the items_page and next_items_page API calls.

I have shared the Power BI query and related three functions that I used to be able to retrieve a board and the cursor that is used with the next_items_page API call to get the next page of data.

Power BI sample with functions and query

There is a Word document with screen shots and instructions on how to use the functions and queries. I also have included a .PBIX file so that you can quickly add your authorization keys and board number.

Hopefully this saves you some time if you are using Power BI to access your board data. January 15th is coming fast!

Cheers.

5 Likes
2

Thank you @hbouk for sharing this!!

1 Like
3

Hi @hbouk
Very much appreciated that you shared that with everyone! thank you :slight_smile:

1 Like
4

Thanks Matias. I wanted to create a query that could loop until the cursor returns as null but Power BI queries don’t support loops. I might look at redoing this in Python so that I can retrieve any number of pages. It will depend on when I have time to get back to this.

2 Likes
6

Awsome post @hbouk, thank you.

I’m using your querry here and is working, but I’m having difficulty to implement something more. I’m trying to filter the items of a board direct in the API, like this:

let
ColumnDate = “data”,
InicialDate = “2023-01-01”,
FinalDate= “2024-12-31”,

MondayQuery1 = "{""query"": ""{boards(ids: ",
MondayQuery2 = Number.ToText(BoardID),   
MondayQuery3 = ") {items_page(limit: 100, query_params: {rules: [{column_id:" & ColumnDate & ",compare_value: [" & InicialDate & ", " & FinalDate& "],operator: between}]}) {cursor items {id name column_values {text column {title}}}}}}""}"

These querry params arguments are exactly how described in the API documentation and they’re working in the API Playground also, but when I try to do this in Power BI it just don’t work. Do you know how to solve this?

7

Hi @JonatasAlmeida. I’ll have a look this Friday. I ran into problems where my queries worked in the API playground but not Power BI as well. I started using postman to test my queries and found it helped a lot.

Any chance you can export your board and give my 1 row of fake data?

8

Thanks for the reply @hbouk, here is an example of my board:

Name New service provider? Company identification code Type of service provider Service provider E-mail Requestor’s email Requestor’s department Attachments Registration Date Due Diligence Comments Deadline Diligence Attachments Diligence Responsible
TEST DATA Yes 0000000000 Test company teste@gmail.com teste@gmail.com Test department 2022-12-08 Approved 2022-08-30 test person
9

I had a look at using the query_params filtering and in the end I didn’t have any success.

One issue I noticed right away was the three variables you set up (ColumnDate, InicialDate, FinalDate) all need their values tripple quoted so that they are concatenated as “data” not data.

Eg.

ColumnDate = “”“date_1"”“,
InitialDate = “”“2023-12-13"””,
FinalDate= “”“2024-06-15"”",

MondayQuery1 = “{”“query”": “”{boards(ids: ",
MondayQuery2 = Number.ToText(BoardID),
MondayQuery3 = “) {items_page(limit: 100, query_params: {rules: [{column_id: " & ColumnDate & “, compare_value: [” & InitialDate & “, " & FinalDate& “], operator: between}] }) {cursor items {id name column_values {text column {title}}}}}}””}”,

Once these are concatenated you get a string like this:
{“query”: “{boards(ids: 0123456789) {items_page(limit: 100, query_params: {rules: [{column_id: “date_1”, compare_value: [“2023-12-13”, “2024-06-15”], operator: between}] }) {cursor items {id name column_values {text column {title}}}}}}”}

But when Power BI sends this to Monday I get the error" DataSource.Error: Web.Contents failed to get contents from ‘https://api.monday.com/v2’ (500): Internal Server Error"

I ran into a similar problem when I was originally developing my Power BI queries and kept fiddling with the syntax until I eventually eliminated the error.

As I was testing it occurred to me that the simpler approach might be to not using the API filtering and bring back all rows of data then filter in Power BI where you have WAY more control.

I’m not impressed with the API filtering. Sometimes I had to filter using an index number (eg. a status column) for the value instead of the text value. Also, I found that I had to use the API playground or Postman to pull some data so that I could determine the column id value as this is different than the text name of a column.

I’m not sure if the filtering capability is poorly developed or is a limitation due to GraphQL. Either way, based on trying to figure this out for you, I’m not going to touch the API filtering in the future.

1 Like
10

Have you looked into using GraphQL variables within Power BI? You’d need to use Json.FromValue I think to create actual JSON from an object that consists of a query string and a variables object. This might help eliminate the concatenation and issues with trying ot ensure things are perfectly escaped.

12

This is really helpful however I keep on getting a “Formula.Firewall: Query ‘Monday Board’ (step ‘2nd_Page_Table_Updated’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.” error, and I’m not quite sure what is causing it. I have followed the instructions exactly and have copied the permission settings. Any help would be great. Thanks!

14

Hi @tomhaywts . I see you withdrew your question but I am going to summarize so that others can quickly resolve the problem if they encounter it. Tom was getting an error in Power BI → "Formula.Firewall: Query ‘M365 Schedule’ (step ‘2nd_Page_Table_Updated’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

This error has to do with privacy levels in your Power BI queries. It can be solved by going into Power BI, click on File then Options and Settings. Choose the ignore privacy levels option under the Current File Privacy settings.

image
image1608×1752 204 KB

15

Hi @codyfrisch. I haven’t tried using Json.FromValue specifically but did spend about a week playing around with syntax to be able to successfully issue a query to the Monday.com API endpoint. My approach was based on an approach I found on Github by @dipro who works for Monday.com. I assumed he would have had some help from the Monday.com developers to figure out an approach that would work.

My approach works but doesn’t scale well if you have very large boards. It solves my needs for the time being but I can forsee needing to redevelop this at some point.

16

Thanks, Howard. That seemed to have fixed my issues!

1 Like
17

I suspect if you’d gone the Json route you’d have had about 2 hours invested in syntax :wink: Getting all the quotes and backslashes just right is a pain. Reach out when you want to work on the redevelopment if you need any assistance.

19

Hi @hbouk ,

Appreciate your sample PowerBI file for looping through to get the various page results, however my board require going into the subitems details to get the necessary information and I’m not able to retrieve the cursor dynamically by using your method, would appreciate your advice on this, I’ve updated your function as this →

MondayQuery1 = “{”“query”“: “”{boards(ids: “,
MondayQuery2 = Number.ToText(BoardID),
MondayQuery3 = “) {items_page(limit: 100) {cursor items {id subitems {id name column_values {column {title} text}}}}}}””}”,
MondayFullQuery = MondayQuery1 & MondayQuery2 & MondayQuery3,
Source = Web.Contents(“https://api.monday.com/”,
[
RelativePath=“v2”,
Headers=
[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“”,
#“API-Version”=“2023-10”
],
Content=Text.ToBinary(MondayFullQuery)
]
),
#“JSON” = Json.Document(Source, 65001),
data = JSON[data],
boards = data[boards],
boards1 = boards{0},
item_page = boards1[items_page],
items = item_page[items],
#“Converted to Table” = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“Expanded Column2” = Table.ExpandRecordColumn(#“Converted to Table”, “Column1”, {“id”, “subitems”}, {“Column1.id”, “Column1.subitems”}),
#“Expanded Column1.subitems2” = Table.ExpandListColumn(#“Expanded Column2”, “Column1.subitems”),
#“Expanded Column1.subitems3” = Table.ExpandRecordColumn(#“Expanded Column1.subitems2”, “Column1.subitems”, {“id”, “name”, “column_values”}, {“Column1.subitems.id”, “Column1.subitems.name”, “Column1.subitems.column_values”}),
#“Expanded Column1.subitems.column_values2” = Table.ExpandListColumn(#“Expanded Column1.subitems3”, “Column1.subitems.column_values”),
#“Expanded Column1.subitems.column_values3” = Table.ExpandRecordColumn(#“Expanded Column1.subitems.column_values2”, “Column1.subitems.column_values”, {“column”, “text”}, {“Column1.subitems.column_values.column”, “Column1.subitems.column_values.text”}),
#“Expanded Column1.subitems.column_values.column” = Table.ExpandRecordColumn(#“Expanded Column1.subitems.column_values3”, “Column1.subitems.column_values.column”, {“title”}, {“Column1.subitems.column_values.column.title”}),
#“Renamed Columns1” = Table.RenameColumns(#“Expanded Column1.subitems.column_values.column”,{{“Column1.subitems.column_values.column.title”, “Column1.subitems.column_values.title”}}),
#“Filtered Rows” = Table.SelectRows(#“Renamed Columns1”, each ([Column1.subitems.column_values.title] <> “Remarks”)),
#“Pivoted Column” = Table.Pivot(#“Filtered Rows”, List.Distinct(#“Filtered Rows”[Column1.subitems.column_values.title]), “Column1.subitems.column_values.title”, “Column1.subitems.column_values.text”),
#“Renamed Columns” = Table.RenameColumns(#“Pivoted Column”,{{“Column1.subitems.id”, “Subitem ID”}, {“Column1.subitems.name”, “Subitem Name”}, {“Column1.id”, “Item ID”}})
in
#“Renamed Columns”
in Source

20

Hi @susenwah. I have been avoiding the use of subitems and so far have had minimal need to make use of them. When I first started using Power BI with Monday I quickly saw that subitems were going to be a problem.

The problem with with subitems in my experience is that you need to bring them back into a separate table in Power BI because the columns for the subitem are likely different. This means you have parent items with the column values in one table and need to be able to link the subitems back to the parent items.

When I looked at this 6 months ago I didn’t fine an easy way to link the subitems and items. Since then I have learned how to use Postman ( https://www.postman.com/) and this has helped me immensely to explore the API and play with queries. The query below will pull the information needed but can’t be used in this form. The subitems portion of the query will have to be it’s own query and then you will need to use the parent_item ID to link to the actual parent item record.

image
image722×1196 64 KB

I think I will have to deal with this soon myself and if so I’ll share the queries. This is definitely going to be more complex. The API structure is unfortunate for those of us who use Power BI.

22

@hbouk I am new to Monday’s API. I was tasked with bringing the info into Power BI. I have the basics down, but what do you mean by subitems? Are you talking about columns that are linked to another board? If so, how does the API know the ID of the second board?

23

@NEI_KGuthrie it is possible that I misunderstood your problem. In your initial post you mentioned “my board require going into the subitems details to get the necessary information” and I took this to mean that you have a board with items and some of those items have subitems. See the image below from one of my test boards.

image
image3908×786 270 KB

In this example there are rows of subitems underneath the parent item called Sunrise Challenge.

Unfortunately, the new version of the API added complexity with the implementation of the cursor value. It gets more complex with subitem records.

I looked at how you configured your query and tested against my test board.

image
image616×808 41.2 KB

When I issued this query it retrieves the subitems but not the parent items.

Maybe you want this query to retrieve the parent item and subitem?

image
image676×1112 57.1 KB

24

Thanks @hbouk for the advice, I managed to get it out based on your previous items example. Yes, I agree that the API structure is very difficult for retrieval from Power BI

26

Thank you for sharing this with us. Do you have any ideas on how I can modify the suggested API query to also include group names (e.g., Q1, Q2, Q3)? As it stands, the suggested query focuses solely on returning columns. I appreciate your assistance!

Board example
Board example2306×1199 162 KB

My API code that needs tweaking to include groups:

let
MondayQuery1 = “{”“query”“: “”{boards(ids: “,
MondayQuery2 = “MY BOARD ID”,
MondayQuery3 = “) {items_page(limit: 100) {cursor items {id name column_values {text column {title}}}}}}””}”,
MondayFullQuery = MondayQuery1 & MondayQuery2 & MondayQuery3,
Source = Web.Contents(“monday.com | A new way of working,
[
RelativePath=“v2”,
Headers=
[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“MY API TOKEN”,
#“API-Version”=“2023-10”
],
Content=Text.ToBinary(MondayFullQuery)
]
),
#“JSON” = Json.Document(Source, 65001),
data = JSON[data],
boards = data[boards],
#“Converted to Table” = Table.FromList(boards, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#“Converted to Table”