Part 3: Building a Power BI Data Model with Trello data

[factgasm]

Now that Trello and Power BI are talking to each other, we need to give them something to talk about. To do this follow these instructions.

In Power BI, click:

The next three steps are the most important we shall cover:

• Scroll down to the field cards.

• Right click on List to bring up this menu:

• Click Add as New Query.

NB all of your changes from this point on will be on the new query. This is cool because it means that we have drilled into the data. Next we need to turn this into a table that we can work with.

Fortunately, Power BI does this for us:

• Click the below and choose the default options (if this does not appear first time it can be found under the Transform tab).

• At first it looks like this has made no difference, but observe the in the first column name. Click it (choose the default options).

  At this point take a moment to look at the right hand side of the screen and notice that Power BI has completed a list of the steps taken so far. Great news, it will perform them every time you refresh the report – you don’t have to do anything – and hence all of your Trello data is there (and brilliantly Trello remains the single version of the truth).

• Before leaving this query also note the columns that we could expand further e.g. we will be returning to some of these later when we start doing some data modelling.
• Press Close & Apply.
• Click edit queries again making sure Trello Parent is chosen (on the left hand side) query and repeat the above steps for the following fields:
  1. labels
     
  2. lists
     
  3. members
     
  4. checklists
• If you have not already done so, click Close & Apply.
• If any of your lists are empty then see: Dealing with gaps in the Trello data (SQL vs NoSQL).
• Press Edit Queries and return to your original query (the one we called Trello Parent) and
  1. Right click on it and Copy
  2. Paste
• You should now have a query called Trello Parent (2)
• Select it
• Rename this Trello Master Table
• Press Convert to Table
• Then go to the Transform tab and click Transpose

• Now click Use First Row as Headers. Your query should look like this:

• Close & Apply

At this point the key elements of Trello data are available to us in tables. However the tables are at this juncture unaware of each other. We will address this in the next section when we will be Building a Trello Data Model in Power BI