Part four: Building a full Power BI data model from the Trello data

[factgasm]

Once your Trello data is accessible within Power BI it is necessary to make it self-aware. That is, the lists, labels and checklists need to know to which cards they belong; the cards need to know which list they are in. That is, we need to build a data model.

At this point the fields available in Power BI may look something like this:

• Click on the Data icon on the left-hand side of the screen:

• Look at the data in each table for a minute or two and satisfy yourself that it “looks like” Trello.
• Now click on the Relationships icon on the left-hand side of the screen (please ignore the icons).

• You should see something like the below

• If there are any lines between the tables just right click and delete them (Power BI sometimes tries to auto-detect but in my experience this is ropey).
• Now let’s build some relationships (for Microsoft’s generic guide on how to do this see here).
• Take the id column from the Trello Master Table, click on it and drag to the Column1.idboard column in the lists table. This is called joining two tables:

• A line will appear between the two tables, double click and make sure that the box that appears looks like the below:

• Repeat the previous three steps, joining the Trello Master Table to the following tables:
  1. labels
     
  2. cards
     
  3. checklists (if available)
  4. NB do not attempt to do this to members
• Your relationships should now look like this (notice that members is floating in space right now):

• Now double click on each relationship and set each relationship to inactive:

At this juncture, were our relationships able to speak, all they would be able to tell us would be: I’m a data model with lists, labels and cards. It would struggle to communicate that, say, this card belonged to this list. We will now amend the model to move it from any old board to your board.

• Now, drag Column1.idlist in the cards table to Column1.id in lists, like so:

(Technical point: we have now covered all of the one-to-many joins and from now on deal in many-to-many joins.)

We now need to bring members in from the cold. To do this we need to build another table.

Within Power BI, do the following:

• Edit Queries.
• Click on Trello Parent (NB not Trello Master Table).
• Right click on the word List (next to cards) and select Add as New Query like so:

• Convert to Table (accepting the default options as you do).
• Expand column 1 by clicking on this time only select the columns:
  1. id
  2. idMembers
• Now expand column1.idMembers by clicking on .
  1. Expand to New Rows

• Rename the query cards x members.
• Close and apply.
• Now go to relationships.

• Join cards x members respectively to cards and members like so (if Power BI has already guessed at a relationship then delete it):

• Double click each relationship and check that:
  1. the Cardinality is One to Many.
  2. the Cross filter direction is Both.
  3. (again do not trust Power BI to guess correctly for you).

We now have a data model that can handle the fact that there are/can be many members per card.

• Repeat steps 1 to 11 for labels and checklists so that you end up with queries called: card x labels and card x checklist.
  

Thus far we have tried to go with the Power BI default as much as possible. However some of our names are ambiguous. You may find it useful to rename some of the columns to something more descriptive.

At this point we have a data model we can play with. Wa-hey!

Challenge: a slight variation on this theme is the way attachments on cards work. Attempt to get them into the above model (you can see my tables at the bottom right).

[Anonymous]

Great guide, but your screenshots are really small 😛 

Makes it hard to reference. Good point on the cardinality. PowerBI defaulted the relationships to one-to-one, so you do need to change them manually and delete the ones it tried to create automatically (which were irrelevant).