Create Trello Reporting using Power BI
Where I am consulting, they use Trello boards which enables them to keep track of what tasks are being done, getting done and how things are progressing.
An interesting question came up asking how much work has been done. And I thought this could be done via Trello and looking at the number of tasks in the boards that have gone from To Do to Completed.
Below are the steps of how I completed this.
Getting the Trello Key and Token
The first step is that I had to get the Trello Key and Token which will allow me to query the Trello API
- The first thing I did was log into Trello by going to https://trello.com/
Next, I went to https://trello.com/app-key to get the developer key
- I made a note of the Developer API Key
Now an important step which I initially missed was I had to click on Token
I was then taken to another window and I saw the following below.
I scrolled to the bottom and clicked on Allow
I then got the Token. I then made a note of the Token
Finding the Trello Board Key
In order to report on the data, I had to find the Trello Board Key, I did it by doing the following below.
In Trello I clicked on Boards
- Then navigated to the board where I wanted to complete my reporting on.
I then got the Trello Board Key from the following part of the URL
- I made a note of foufGQgY which is the Trello Board Key
Importing the Trello data into Power BI
Below are the steps I completed to import the data into Power BI.
I created the following Parameters
TrelloAPIKey – Which I made a note of in the earlier steps
TelloAPIToken – Which I made a note of in the earlier steps
TrelloBoardKey – Which I made a note of in the earlier steps
I then went through the steps to extract the Trello data. I also created another table which got the Trello lists. This was all merged into one final table called Trello Data
- There are a fair few steps here, but it allows for me to see all the data I required. Your reporting requirements might be different, and you can adjust it as required.
These were the 2 tables that I created, with only Trello Data being loaded.
- I then loaded the data into the data model.
Creating the Power BI Trello Report
The next step is when I created the Trello report. For my reporting requirement was I wanted to display the data in the DOING board and COMPLETED Board.
- As shown below I created the 2 tables and filtered them by the Board Name
- And this is what my Report looked like.
Configuring the Power BI Gateway for the dataset to refresh
Of course, I wanted my data to be refreshed daily so I set it up using the Power BI On-Premise Gateway as shown below.
- I went to the Power BI Manage Gateways in the Power BI Service.
- I had to create 2 data sources, one for each of the tables I am connecting to in Trello.
Refreshing the data in Power Query
If you go back into Power Query and want to refresh the data you might be presented with the screen below, where it is asking for Edit Credentials.
Click on Edit Credentials and make sure that it is set to Anonymouse and click Connect as shown below.
The reason that Anonymous still works is because the API contains all the connection details already.
In this blog post I have shown how to connect to Trello data from Power BI, create the dataset and then the reporting.
Here is a link to the Power BI Template which you can use for your reporting: Trello Reporting.pbit
When opening the Power BI Template file, please put in the following Parameters as explained above.
As always thanks for reading and if you got any questions please let me know in the comments section below.