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]
#“Monday” = Table.FromRecords(Table.TransformRows(Data, each

List.Accumulate(�Columns], F

ID = .ID],

Title = hTitle],

UpdateDate = uUpdatedAt],

Group = rGroup]>title]

], (state, current) => Record.AddField(state, currentGtitle], current>text]) )

))

in

#“Monday”

Page 1 / 1

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

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

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.

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

Hello again,

I am glad that is the case @Demmo 😁

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

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]bboards]{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 (etitle.1] <> “Subitems”)),

#“Pivoted Column” = Table.Pivot(#“Filtered Rows”, List.Distinct(#“Filtered Rows”=title.1]), “title.1”, “text”)

in

#“Pivoted Column”

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!

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(){kName="SessionBoardID"]}DContent]{0}]Column1]),

    

        Source = Web.Contents("https://api.monday.com/v2",



              Headers = s #"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)rdata]aboards]{0}]items_page]aitems], 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({ntext], tdisplay_value]}), type text),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom Column",{"text", "display_value"}),

    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"mtitle.1]), "title.1", "Custom"),

    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Title", "Name"}})

in

    #"Renamed Columns"

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”

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.

Reply

Sign up

Login to monday.com

Scanning file for viruses.

This file cannot be downloaded