Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
erikgroupo
Frequent Visitor

Struggling using API bringing in subitems from Monday board using M

Greetings! I have been using PBI for about 8 months, and dont have a lot of experience with M.
We have some Monday boards that use subitems and we are having a hard time bringing those in to power bi. We found some sources online that "Adapt" our Monday board as a web URL and load it that way, but we would rather be able to run it locally without relying on outside sources. Any suggestions on how to bring in sub items without manually typing out each subitem ID's?

 

Here is our M(That I found online):

let
Source = Web.Contents("Board URL" & "v2",
[
Headers=[
#"Method"="POST",
#"Content-Type"="application/json",
#"Authorization"="Monday Personal Token"
],
Content=Text.ToBinary("{""query"": ""{ boards (ids: 5118243624 ) { items { id name column_values { title text } } } }""}")
]
),
#"JSON" = Json.Document(Source,65001),
data = JSON[data],
boards = data[boards],
boards1 = boards{0},
items = boards1[items],
#"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "column_values"}, {"Column1.id", "Column1.name", "Column1.column_values"}),
#"Expanded Column1.column_values3" = Table.ExpandListColumn(#"Expanded Column2", "Column1.column_values"),
#"Expanded Column1.column_values4" = Table.ExpandRecordColumn(#"Expanded Column1.column_values3", "Column1.column_values", {"title", "text"}, {"Column1.column_values.title", "Column1.column_values.text"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Column1.column_values4", List.Distinct(#"Expanded Column1.column_values4"[Column1.column_values.title]), "Column1.column_values.title", "Column1.column_values.text", List.Max)
in
#"Pivoted Column"

 

 

And this is the M we use to bring in sub items seperatly from the main board iteams:

let
Source = Web.Contents("Board URL" & "v2",
[
Headers=[
#"Method"="POST",
#"Content-Type"="application/json",
#"Authorization"="Monday Personal Token"
],
Content=Text.ToBinary("{""query"": ""{ boards (ids: 5118243624) { items (ids:[5162766655] ){ group { id title } name subitems { id name column_values { title text } } } } }""}")
]
),
#"JSON" = Json.Document(Source,65001),
data = JSON[data],
boards = data[boards],
boards1 = boards{0},
items = boards1[items],
#"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"group", "name", "subitems"}, {"Column1.group", "Column1.name", "Column1.subitems"}),
#"Expanded Column1.subitems" = Table.ExpandListColumn(#"Expanded Column1", "Column1.subitems"),
#"Expanded Column1.group" = Table.ExpandRecordColumn(#"Expanded Column1.subitems", "Column1.group", {"id", "title"}, {"Column1.group.id", "Column1.group.title"}),
#"Expanded Column1.subitems1" = Table.ExpandRecordColumn(#"Expanded Column1.group", "Column1.subitems", {"id", "name", "column_values"}, {"Column1.subitems.id", "Column1.subitems.name", "Column1.subitems.column_values"}),
#"Expanded Column1.subitems.column_values" = Table.ExpandListColumn(#"Expanded Column1.subitems1", "Column1.subitems.column_values"),
#"Expanded Column1.subitems.column_values1" = Table.ExpandRecordColumn(#"Expanded Column1.subitems.column_values", "Column1.subitems.column_values", {"title", "text"}, {"Column1.subitems.column_values.title", "Column1.subitems.column_values.text"})
in
#"Expanded Column1.subitems.column_values1"

 

The problem is how with subitems we need to call each individual one, and if anyone adds a new subitem, it wont load into the dataset due to it not being in the list in example 2. 

 

Is anyone familiar enough with M to figure out how to get power bi to see all Subitem ID's without having to list them out individually?

 

We are aware of a few services that we can pay for that will to all the heavy lifting with bringing Monday.com data into Power BI, but we of course would like to figure this out "in house" if possble.

2 REPLIES 2
erikgroupo
Frequent Visitor

Thank you for the reply Xiaoxin,

 

I may have worded the question wrong, and even then, it may have changed. 

 

Here is the M query we are using currently to bring in the subitems. I have contacted Monday.com and they were of assistance directing us how to access all subitems on a board, but I am running into limits on how much I can bring in at once. 

 

(id_board as text) =>

let
Key = "Monday Key",


Source = Web.Contents("https://api.monday.com/",
[
RelativePath="v2",
Headers=[
#"Method"="POST",
#"Content-Type"="application/json",
#"Authorization"=
"Monday Key"
],
Content=Text.ToBinary("{""query"": ""{ boards(ids: 3305530367) { items (limit: 350, page: 1){ id subitems{name column_values{ title text }} } } }""}")
]
),
#"Json" = Json.Document(Source,65001),
data = Json[data],
boards = data[boards],
boards1 = boards{0},
items = boards1[items],
#"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

 

The bolded section is I believe the issue. That is the limit of subitem data that can come over after we invoke the subitem board id. If we set the number any higher then it give the error:

"An error occurred in the ‘’ query. Expression.Error: The field 'data' of the record wasn't found.
Details:
errors=[List]
extensions=[Record]
account_id=6018786"

 

How can I use M to break the data up to bring in multiple pages of subitem data and not be limited to the 350 max like I am right now?

v-shex-msft
Community Support
Community Support

Hi @erikgroupo,

So you mean the result table structure should dynamic change if the remote data source changes?
AFAIK, current power query will cache the table structures in the query steps and not auto change even if the source table structure changes.
If you want it keep/sync to the lastest structure, you may need to do some customize on power query functions.(e.g. invoke value from the previous step result in the following query steps initializations instead of use static text values)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.