Best way to connect powerbi to Monday

Would be wonderful is monday.com board can act as a data source to PowerBI (and therefore Excel)

We are using Monday + BigQuery + DataStudio in order to get some proper statistics from our activity in Monday.
I set up a process that downloads items from specific boards (using the GraphQL api). I then take the response from that request and upload it to BigQuery as a table. I then query the table and create some views which expose the data I need in DataStudio. Then I connect DataStudio to these views and can make my reports.

I assume a similar approach would work for PowerBI. It is not the simplest solution, but is the only one I found to work that didn’t require writing a lot of code.

Hi Pablo, I was wondering if you managed to connect from PowerBI to monday.com. I’ve been trying it for days but without any success.

Hi

I made a query via python to a postgrSql. And made a odbc connection to that from PoweBI.

I’m still on a testing step, when I’m satisfied, I will move query to onsite servers and wil take use of PowerBI gateway for production .

I had some issues understanding the nature of “boards”. And to be honest, the Monday solution , that it creates board for al and anything , is a mess.

But we solved that (I think) by making some internal guidings for how to stage new boards.

Hope this was to any help.

Hello!

No, we haven’t. We are still on our BigQuery+DataStudio setup which we use for dashboards and KPIs.

Hey y’all!

I just hopped off a call with a client who had been working with PowerBI and monday.com. We built a query together that pulled monday.com board data into a JSON object to be parsed by BI.

Here’s the code: Loading GraphQL data (monday.com API v2) into PowerBI · GitHub

I hope that helps 🙂

Cheers,
Dipro 🔮

@serguei.kolomeitsev @PabloVerano @lerheim @basdebruin

Hi Dipro,

Thanks for sharing. This is really helpful. I appreciate it.

regards,
Serguei

This is excellent!

A tip: I ignore your use case, but if you are handling boards with thousands of items, you might need to tweak the query to handle paging correctly.

Good call, @PabloVerano!

Hi All. Jumping onto this thread with just my “two-cents”. I think the best way would be to have Monday integrated with Power Automate (MS Flow), which in turn is completely integrated with Power Bi. If interested, you can vote up my feature request for this integration.

I’ve improved @dipro script to automatically explode Monday columns in a table: Power BI Monday.com Query - Power Query M · GitHub

I’ve also created a NodeJS script and pushed it to Heroku to make it easier to import and format Monday items: GitHub - Guichaguri/MondayPowerBIAdapter: Adapts a Monday board to Power BI
It also generates a token that only gives access to a single board, so you don’t have to expose your API Key in your report. The key is stored in a database, but if you want you can take the code and host yourself.

Do you mind explaining your lines of code? It’s nearly perfect for what I need but I’d like to understand it so I can make some modifications if need be.

Line 8 to 18 fetches from the Monday API
Line 19 converts the data inside “data -> board-> items” to a table with the column names “Title”, “Updated At”, “Group”, “Columns”

The magic is in line 20 to 26:

1. Loops every row in the table using the TransformRows function
   • For every row, it creates a record with the “Title”, “Updated At” and “Group” columns
   • Loops the “Columns” data, appending the title and text of each one to the record
2. Converts the records back to a table

The script worked great thank you!!
Do you know if there is a limit on the number of rows it can receive in PBI?

The limit is 16000 columns, I think you should be good

This is great! Thank you for sharing. Is there a way to pull text from a formula column in Monday?

Hey Mitchell, the formula column is not supported by our API at the moment.

Our API only can access data that is in our database, but currently the formula column is calculated in the client (ie, when you open the board).

We’re working on adding this to our API, but it’ll take some work to adjust our infrastructure accordingly. Until then, I’d recommend building the formula from scratch in your code (retrieve the relevant column values and then do the calculation in your app).

I was able to use your (Guichaguri) supplied code to get our specific Monday board to load data into PowerBI. I am really new/green to all this (Monday and PBI), but am trying to learn.

Quick question…should I be able to limit the results to a specific group in my board if I just add a group id filter to the code, or is there more to it? I used GraphQL and got a query to do exactly what I need, but when I try to edit the query from your supplied script, I get this error when it tries to refresh the dataset:

DataSource.Error: Web.Contents failed to get contents from ‘https://api.monday.com/v2’ (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.monday.com/v2
Url=https://api.monday.com/v2

All I am doing is adding the groups filter after the board is called. Taking this line from the original code:

Content=Text.ToBinary("{""query"": ""query { boards(ids: " & Board & ") {{ items { name, updated_at, group { title }, columns: column_values { title, text } } } }""}")

And updating it to this to call the “topics” group only:
Content=Text.ToBinary("{""query"": ""query { boards(ids: " & Board & ") {groups(ids:""topics"") { items { name, updated_at, group { title }, columns: column_values { title, text } } } } }""}")

If I change the code back, it loads all the groups in my board just fine like before, but I just want data for the one specific group, but am having trouble understanding why the query works natively in GraphQL, but not in this code-set when loaded in PBI.

I appreciate any help/suggestions! Thank you!

Guys could you share some insights your are looking to get from the use of Power Bi ?

Can anyone tell me what I am doing wrong here? I change the key to 123

let
Source = Web.Contents(
“https://api.monday.com/v2”,

    [
           Headers=[
                   #"Method"="POST",
                   #"Content-Type"="application/json",
                   #"Authorization"="12345"
           ],
// Notice the quote escaping here
           Content=Text.ToBinary("{""query"": ""{ boards (ids: 1234) { items { name id column_values (ids:[long_text] { id title value } } } }""}")
    ]
),
#"JSON" = Json.Document(Source)

in
#“JSON”

This is what I get:

In post man, I can get data using Get … but in Power BI it seems off.

@ceyres

That does seem quite odd - could you reach out to us at support@monday.com so we could gather a bit more information from you?

Just to clarify, has this exact code query been working without issues for you before? Is the ID of the long_text column matching the ID within your board?

I’d love to continue tackling this via email so that we could investigate this further for you. I hope that makes sense.

-Alex

Hi Alex,

I reached out to the support team.

To answer you question, I turned on the developer mode. Looked at the column name by right clicking on it. Also, I never been able to load into Power BI. This is a new project.

Thanks!