How to return Board Name and ID in my Power BI query?

Platform discussions
, ,
1

Hi all, I need to adjust my PBI query to handle the monday api output which now includes the board name and id. Can someone tell me what I need to add in please, many thanks!

“{”“query”“: ““query {boards(ids: “& Board &”) {name id items_page(limit: 500) {items {id name updated_at group {title} column_values {column {title} text … on MirrorValue {display_value} … on BoardRelationValue {display_value}}}}}}””}”)]),
Data = Table.FromList(Json.Document(Source)[data][boards]{0}[items_page][items], Record.FieldValues, {“BoardName”, “BoardID”,“ID”, “Title”, “UpdatedAt”, “Group”, “Columns_Values”})
in
Data

2

@codyfrisch take a look if you can help.

3

@hbouk I’m looping you in since you seem to be the Power BI guru here!

@Demmo I’d be very cautious with a limit of 500 and the ... on MirrorValue there have been performance issues around this, depending on when the number of mirror columns increases. Its sometimes bad enough that people can only get 10-20 items at a time or else the query errors or timesout.

@rehan.abdul just an FYI, while I seem to be everywhere and know a lot about the API, I don’t actually work for monday! and Power BI is probably the one thing I barely know - just enough to be very dangerous with it.

4

I know, I just tagged you because you are normally on discussions of API :stuck_out_tongue: .

5

I haven’t had to deal with connected boards so far. :grinning: I have avoided this (to keep things simpler) by adding a column in each of the boards I need to “join” that I can use in Power BI to join the boards. Generally, I treat each board as a table then handle all joins in Power BI.

I keep running into Monday functionality (mostly column types) that are supported in Monday but not through the API so I end up coming up with what simple work arounds.

6

Thanks all - I’m just after the Board name and id being returned as part of my existing query. The Graph QL bit is fine - it’s just handling the output in the M Query syntax that I don’t get… i.e. the additional 2 columns that are being returned from monday…

7

When you extract data using Table.FromList(Json.Document(Source)[data][boards]{0}[items_page][items], Record.FieldValues, {“BoardName”, “BoardID”, “ID”, “Title”, “UpdatedAt”, “Group”, “Columns_Values”}), you’re focusing on the items within a single board, which might not include the board’s own metadata like its name or ID. To access the BOARDID and BOARD NAME, you should first retrieve the board-level details. Use Table.FromList(Json.Document(Source)[data][boards], Record.FieldValues, [“BoardName”, “BoardID”, …]) where ‘…’ represents other relevant fields you might need from the board level. This method will help you access the BoardID and BoardName before you dive into the specific items of each board.

I’ve never used PBI, but the approach to retrieving information via JSON is a standard structure."

1 Like
8

Thanks @enier - I’ve gone with 2 separate queries and combined them in PBI to get the desired result.

9

Is it working as you needed ?

10

@enier - yes, all good. I’m not an experienced query writer, so I’m always trying to return everything in one hit, where a multi-stage approach is required/just as good!