Struggling using API bringing in subitems from Monday board using M

[erikgroupo]

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.

[erikgroupo]

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]

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