API Changes - Help needed to update favourite query!

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”

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

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?

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

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’.

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

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

1 Like

Hello again,

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

1 Like

@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

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

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

so @bsgilli - I now need the same help with mirror columns…! I can get my data back in API playground with display_value etc - but struggling with the rest of the script to create table for Power BI consumption… do you have an example?

@Demmo - happy to share back! I think I still need to work on pagination, but I don’t have enough data right now for that to be an issue. Here’s the code:

let
    
    Key = "XXXXXXXX",
    Board = Number.ToText(Excel.CurrentWorkbook(){[Name="SessionBoardID"]}[Content]{0}[Column1]),
    
        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 {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, {"Title", "UpdatedAt", "Group", "Columns_Values"}),
    #"Expanded Group" = Table.ExpandRecordColumn(Data, "Group", {"title"}, {"Group"}),
    #"Expanded Columns_Values" = Table.ExpandListColumn(#"Expanded Group", "Columns_Values"),
    #"Expanded Columns_Values2" = Table.ExpandRecordColumn(#"Expanded Columns_Values", "Columns_Values", {"column", "text", "display_value"}, {"column", "text", "display_value"}),
    #"Expanded column" = Table.ExpandRecordColumn(#"Expanded Columns_Values2", "column", {"title"}, {"title.1"}),
    #"Added Custom Column" = Table.AddColumn(#"Expanded column", "Custom", each Text.Combine({[text], [display_value]}), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"text", "display_value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[title.1]), "title.1", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Title", "Name"}})
in
    #"Renamed Columns"
1 Like

thanks @bsgilli - except it only seems to work with a small record limit? (say 5) - which I can see is happening to others on the forum… I’m getting
“message”: “Unexpected token ‘u’, "upstream r"… is not valid JSON”,
“stack”: “SyntaxError: Unexpected token ‘u’, "upstream r"… is not valid JSON”

1 Like

Hello @Demmo ,

I’m using this exact query and are having the same issue as you. What is the exact query you are using in advanced editor now in PowerBI to replicate this old query?

Thanks in advance!

Hi @dmarino13 - so, the issue was only temporary for me. This is my go to basic query now:

let
  Key = "XXXXX",
  Board = "XXXXX",
  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 ...on MirrorValue {display_value} ...on BoardRelationValue {display_value}}}}}}""}")]),
  Data = Table.FromList(Json.Document(Source)[data][boards]{0}[items_page][items], Record.FieldValues, {"ID", "Title", "UpdatedAt", "Group", "Columns_Values"}),
  #"Expanded Group" = Table.ExpandRecordColumn(Data, "Group", {"title"}, {"Group"}),
  #"Expanded Columns_Values" = Table.ExpandListColumn(#"Expanded Group", "Columns_Values"),
  #"Expanded Columns_Values2" = Table.ExpandRecordColumn(#"Expanded Columns_Values", "Columns_Values", {"column", "text", "display_value"}, {"column", "text", "display_value"}),
  #"Expanded column" = Table.ExpandRecordColumn(#"Expanded Columns_Values2", "column", {"title"}, {"title.1"}),
  #"Added Custom Column" = Table.AddColumn(#"Expanded column", "Custom", each Text.Combine({[text], [display_value]}), type text),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column", {"text", "display_value"}),
  #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[title.1]), "title.1", "Custom"),
  #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column", {{"Title", "Title"}}),
  #"Transform columns" = Table.TransformColumnTypes(#"Renamed Columns", {{"ID", type text}, {"Title", type text}, {"UpdatedAt", type text}, {"Group", type text}}),
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"ID", null}, {"Title", null}, {"UpdatedAt", null}, {"Group", null}})
in
  #"Replace errors"

Hi there. I’m getting the same error.

I’ve been using this API for several months and never had a problem… can you help me please?

Thanks

Hi @goncalocsqueiros - I can only refer you to my previous post above with the usual code I use for a basic retrieval. I haven’t had any performance issue for ages - but my boards are rarely above 300 items.