API Changes - Help needed to update favourite query!

monday Apps & Developers
,
1

Hi - I am not an experienced query writer and just need my favourite query updated to use the new API. Can anyone update this one for me? Ta!

let
Key = “”,
Board = “”,
Source = Web.Contents(
“https://api.monday.com/v2”,
[
Headers=[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“Bearer " & Key
],
Content=Text.ToBinary(”{““query””: ““query { boards(ids: " & Board & “) { items { id, name, updated_at, group { title }, columns: column_values { title, text } } } }””}”)
]
),
Data = Table.FromList(Json.Document(Source)[data][boards]{0}[items], Record.FieldValues, {“ID”, “Title”, “UpdatedAt”, “Group”, “Columns”}),
#“Monday” = Table.FromRecords(Table.TransformRows(Data, each
List.Accumulate([Columns], [
ID = [ID],
Title = [Title],
UpdateDate = [UpdatedAt],
Group = [Group][title]
], (state, current) => Record.AddField(state, current[title], current[text]) )
))
in
#“Monday”

2

Hello there @Demmo,

You can use a query like this one:

{
  boards(ids: 1234567890) {
    items_page {
      items {
        id
        name
        updated_at
        group {
          title
        }
        column_values {
          column {
            title
          }
          text
        }
      }
    }
  }
}

Let me know how that goes!

Cheers,
Matias

1 Like
3

Hi Matias - I’m getting this message:
Expression.Error: The field ‘data’ of the record wasn’t found.
Is there something in the second half of the query that I need to be modifying to match your changes?

4

Hello again @Demmo,

You would need to check what you get as a response but it shouldn’t be different. The body of the response will start with:

{
    "data": {
        "boards": [
            {
                "items_page": {
                    "items": [...

But check the body of the response you get and you will see why you are getting that error.

Cheers,
Matias

1 Like
5

API_Query_Issue

This is all the error message gives me. I should have said that I am querying from Power BI. The query you gave me works fine in the monday developer playground (thankyou!), so I am assuming there is something in the rest of my query that is not right… the JSON part…where it defines ‘Data’.

6

Hello again,

I am not experienced with Power BI. Let’s hope someone in the community is and can help you with that.

But in all cases, it looks like your script is trying to use the “data” field from the response and not finding it. Do you have the possibility to log the full response you get from monday (before your script tries to get the “data” field)? You might be able to check why it is not finding it in that way.

1 Like
7

Many thanks for your help Matias - I have managed to get the final bit of the jigsaw!

1 Like
8

Hello again,

I am glad that is the case @Demmo :grin:

1 Like
9

@Demmo Hi Graeme! I’m encountering the same issue with updating my power query code. Can you share how you found the solution? Thanks!

1 Like
10

Hi Beth - try this… I hadn’t reflected the items_page change later in the code

let
Key = “xxx”,
Board = “xxx”,
Source = Web.Contents(
“https://api.monday.com/v2”,
[
Headers=[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“API-Version”=“2023-10”,
#“Authorization”=“Bearer " & Key
],
Content=Text.ToBinary(”{““query””: ““query { boards(ids:” & Board & “) { items_page (limit: 500) { items { id, name, updated_at, group { title }, column_values{ column { title } text } } } } }””}")
]
),
Data = Table.FromList(Json.Document(Source)[data][boards]{0}[items_page][items], Record.FieldValues, {“ID”, “Title”, “UpdatedAt”, “Group”, “Columns_Values”}),
#“Expanded Columns_Values” = Table.ExpandListColumn(Data, “Columns_Values”),
#“Expanded Group” = Table.ExpandRecordColumn(#“Expanded Columns_Values”, “Group”, {“title”}, {“Group”}),
#“Expanded Columns_Values1” = Table.ExpandRecordColumn(#“Expanded Group”, “Columns_Values”, {“column”, “text”}, {“column”, “text”}),
#“Expanded column” = Table.ExpandRecordColumn(#“Expanded Columns_Values1”, “column”, {“title”}, {“title.1”}),
#“Filtered Rows” = Table.SelectRows(#“Expanded column”, each ([title.1] <> “Subitems”)),
#“Pivoted Column” = Table.Pivot(#“Filtered Rows”, List.Distinct(#“Filtered Rows”[title.1]), “title.1”, “text”)
in
#“Pivoted Column”

2 Likes
12

Thanks @Demmo! Turns out I had the added complication of needing to pull back mirror column data, too, but I’ve worked that out as well using the migration guide. Thanks for sharing your solution!

1 Like