Dear Mondayers!
I have my Power Query almost in place… but the last part is killing me…
can someone please tell me how I include the subitems in my query? What I get now is only the multipe values in 1 display_values cell on item level, not useful ofcourse. How should I amend my code?
let
Key = “…”,
Board = “…”,
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”}),
#“Filtered Rows” = Table.SelectRows(#“Expanded column”, each ([title.1] = “Hours expected”)),
#“Added Custom Column” = Table.AddColumn(#“Filtered Rows”, “Custom”, each Text.Combine({[text], [display_value]}), type text)
in
#“Added Custom Column”
