Power BI Monday.com Query - Power Query M

monday.m

// Queries Monday.com items from a specific board. Limits to 500 items.
// Tested in API version 2024-01
// Created by Guilherme Chaguri
// Replace the API Key and Board ID for it to work
// In Power BI: "Get Data" -> "Blank Query" -> Right click the "Query" in the left panel and open the "Advanced Editor" -> Paste this code

let
	Key = "YOUR MONDAY API KEY",
	Board = "YOUR MONDAY BOARD ID NUMBER",
	Source = Web.Contents(
	"https://api.monday.com/v2",
	[
		Headers=[
			#"Method"="POST",
			#"Content-Type"="application/json",
			#"Authorization"="Bearer " & Key,
			#"API-Version"="2024-01"
		],
		Content=Text.ToBinary("{""query"": ""query { boards(ids: " & Board & ") { items_page(limit: 500) { items { name, updated_at, group { title }, columns: column_values { column { title }, text, ...on DependencyValue { display_value }, ...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"}),
	#"Monday" = Table.FromRecords(Table.TransformRows(Data, each 
		List.Accumulate([Columns], [
			Title = [Title],
			UpdateDate = [UpdatedAt],
			Group = [Group][title]
		], (state, current) => Record.AddField(
			state, current[column][title],
			if Record.HasFields(current, "display_value") then current[display_value] ?? current[text] else current[text])
		)
	))
in
	#"Monday"

@Thehammer53 nice works now, thanks a million!

@Thehammer53

Thanks, but I had te regenerate the API token! Thanks for your reply anyway!

Does anyone else have the issue of some columns not getting pulling through? Apparently formula columns do not work with the API...

@stevenromanous monday.com’s API documentation says that formula Columns are not supported with their API. Monday’s formulas suck anyways. Just create calculated columns

@Thehammer53 but the formulated columns in this one Monday board I have connected to Power BI, is picking up data from other Monday.com boards. Do u foresee a workaround here?

@stevenromanous mirroring the columns might work but you could just create multiple tables in your power BI and create relations between them and then use a calculated column

The thing with creating multiple tables is Power BI is that, I would need to constantly go in there and create new tables for new Monday.com projects. Not sustainable

I will try mirroring columns to see if that works, thanks for your guidance!

This really works, but, how can I use the code to extract subitens?