If you work with Trello, then you will probably agree with me that it is a very powerful tool.
In Madeira Data Solutions, we work a lot with Trello, from managing internal projects to managing our customer relations. It is excellent for operating the on-going activities, with features like checklists and due dates. But the weak part in Trello is reporting.
For our ongoing work, we need reports and dashboards to easily view and analyze the valuable data that we have inside all of these Trello boards. Naturally, we thought about doing it with Power BI.
Unfortunately, It turns out that it's not so easy, and there are several ways to achieve this goal. Here is the full story.
Few feasible solutions:
1. Usage of an outside component
(like TrelloExport – chrome extension) – we can also use export to CSV, but we'll need some additional work on the file, Download the board data into an excel file.
Then you can work a little with the data in your file or load it as it is to the PBI model.
We ran a little excel macro to arrange the data in one flat table (sheet) in the excel and then load it into the model.
PROS:
We all know excel and can easily connect it to PBI.
We can easily understand the data in the excel itself
CONS:
We may have to rearrange the columns inside the excel file to fit our needs (Macro)
We'll have to design the PBI model and parse the data into a star schema model (depends on the complicity of the Trello board and our reporting needs)
This is a physical file residing on our local machine.
The data is not online, and it requires a manual process to refresh it.
May not export all of the desired data.
2. Export of the Trello board to a JSON file.
Click on the menu on the right >> click on "Print and Export" >> then click "Export to JSON"
Download a JSON file with all of the board's elements. This file can be loaded to the PBI model as it is, and you can work your magic with the "Transform data" window (Power Query Editor) to parse this script.
Need more info?
Pete Moore has a great post about connecting Trello JSON data to Power BI
Power BI Community Blog has published a series of posts about parsing the Trello JSON in Power BI
PROS:
This takes the full data of every object on the board.
No need for an outside component
CONS:
Trello JSON export is limited for 1000 elements for every object.
The parsing is a bit of a challenge (more on this in a future post).
This is a physical file residing on our local machine.
The data is not online, and it requires a manual process to refresh it.
3. Using a web API to direct connect Power BI to Trello API
Following this great post in the Atlassian community: How can I connect Trello to Power BI like a Web source?
Just open a new source blank query, and write the power query script, provide it with the web API Key and Token (get it here), and you are good to go.
PROS:
This can take the full data of every object on the board.
It can be used for exporting partial data (filter by an object, column, and other).
This is the only option that can be refreshed live to retrieve data.
CONS:
This can be a little tricky to accomplished (more on this in a future post)
4. Third-party application
The last option I've heard about but didn't have the chance of testing my self is some kind of a third party driver to connect the applications. I'll love it if someone could write he's experiencing with some of them.
Our Solution
We are at Madeira chose to use the Trello API for the full board's data and the option for refreshing the data (refresh schedule in the PBI portal)