Integrating and Visualising Multiple Microsoft To Do Accounts with Power BI

It’s been a while that I use Microsoft To Do to organise my daily tasks. From work-related tasks to buy groceries. While Microsoft To Do is super easy to use but there are some challenges in using it more efficiently, especially when you have multiple O365 accounts within different organisations. Here are some of the challenges I faced; you may face other challenges too:

• The Microsoft To Do app for Windows devices is very user friendly with amazingly good features like the ability to add multiple To Do accounts. However, we currently have to select which account we would like to use and the app shows all our tasks within that specific account. This means we can not see all our tasks from all our accounts in a single place.
• The Microsoft To Do app for iOS devices is also very handy to use, but it lacks adding multiple accounts. Hence we cannot see all our tasks from multiple O365 accounts on the app. 🙁
• We can use the Tasks within the Microsoft Outlook desktop application (I used the Windows version) which is by far the most comprehensive one with tons of features. While we can see tasks from multiple accounts in a single place, it is a real challenge if I want to know which task is assigned to which account. Besides, it is really hard to answer some questions like, how many high-priority tasks I have for today or the week ahead. I know, we can group tasks, but, it is still not so intuitive.

For the above reasons, I searched for a product that can do all the above at once. After spending some hours, I thought, well, I have to do it myself.

With that, let’s go ahead and see how we can get the job done in Power BI.

Note:

This method is not working for Microsoft To Do using personal accounts such as Outlook, Hotmail or MSN. If anyone knows how to add those, please let us know in the comments section below this post.

This is a long post that took me a reasonable amount of time to write. So I added the following table of contents so you can quickly jump to a subject of your interest.

Table of Contents

• How it Works
• Integrating Multiple To Do accounts in Power BI
  • Getting Data from the Source
  • Data Preparation
  • Revisiting the Requirements
• Data Modelling
  • Creating Date Table
    • Marking Date Table as Date
  • Creating Relationships
  • Creating Measures
    • Measures for All Tasks
    • Measures for Today’s Tasks
• Data Visualisation
  • Showing URL Links in Table Visual
  • Filtering the Data to Show Today’s Tasks Only
• Downloading the Sample File

How It Works

Microsoft Power BI is NOT a reporting tool only. We can connect to many data sources, mix and match the data, create data models and visualise the data. So it should be possible to connect to multiple To Do accounts, append the data, create a simple data model on top of that, and visualise the data to answer our questions or our customers’ questions. The Microsoft To Do data is accessible via the Microsoft Exchange Online connector available in Power BI. The rest depends on our requirements and what questions we would like to answer.

In my case, in which I am the end-user of the report, I would like to be able to know:

• Today’s tasks: All tasks that their StartDate or DueDate is today or the Tasks without any StartDate and DueDate
  • Number of tasks
  • Number of important tasks
  • Tasks by mailbox
  • Tasks details
    • Task list
    • Task description
    • Status
    • Start date
    • Due date
    • A link to the task itself that I can update if I want to
• All Tasks
  • All above plus
    • Number of open tasks
    • Number of completed tasks

You or your customer(s) might have different requirements, but once you understand how to get the To Do data from Microsoft Exchange Online and do some data explorations to find out what you are after, you’ll be good.

Integrating Multiple To Do Accounts in Power BI

Before we start, I like to encourage you to change your Power BI settings to allow parameterisation in all connection and transformation dialogue boxes. The following steps explain how to do so:

1. In Power BI Desktop click the File menu
2. Click Options and settings
3. Click Options
4. In the Options, window select the Power Query Editor tab
5. Tick the Always allow parameterization in data source and transformation dialogs option
6. Click OK

With that, let us get the Microsoft To Do data in Power BI Desktop.

Getting Data from the Source

This section explains how to get the data from Microsoft Exchange Online for Microsoft To Do.

Follow these steps:

1. Click Get data in Power BI Desktop
2. Search for Microsoft Exchange
3. Select Microsoft Exchange Online (you can use Microsoft Exchange as well, in our scenario both connections work the same)
4. Click Connect

5. Click the Parameterise drop down button and select the New parameter… option to open the Magane Parameters window. This button is enabled as we previously enabled this feature.

6. Type in a Name for the parameter
7. Change the Type to Text
8. Type in your email address associated with your account in the Current Value
9. Click New and repeat the process from step 6 for creating a query parameter for all To Do accounts you’d like to integrate
10. Click OK

11. Select the desired query parameter in the Mailbox address dropdown from the Microsoft Exchange connector
12. Click OK

We have a few options on the Navigator window. We can analyse our Calendar, Email, etc., but this blog post aims to get the data from Microsoft To Do. I leave that for you to explore more on those areas :).

13. Tick the Tasks table. This is the table that keeps all To Do data 😀
14. Click Transform Data

We are now navigated to the Power Query Editor with 3 queries appearing on the Queries pane; the two query parameters we created earlier and the Task table loaded from Microsoft Exchange Online. Now we have to repeat the process and get the data from our other accounts. After getting data from all our accounts, we have to prepare the data to meet our requirements.

Note:

I renamed the queries to Task 1 and Task 2.

So far, we connected to multiple Microsoft Exchange Online sources. That is the very first step. The next step is to prepare the data to meet our requirements.

Data Preparation

I have to say that the data is well structured already; therefore, we have to spend less effort to prepare the data. The following few steps take care of my requirements which might be different than yours:

1. Right-click each query
2. Disable data load (I wish I could select multiple queries and disable load for all selected queries. If you think this is a good idea that improves the development please vote for this idea which was posted a long time ago.)

I disabled the data load as I require to Append the queries. I prefer to append the queries into a separate query, so it would be easier to make changes in the future if I require to change anything in the original queries. I will revisit this point later in this post.

3. Select a query from the Queries pane
4. Click the Append Queries dropdown button
5. From the Home tab, select the Append Queries as New option

6. Select the first and second queries you’d like to append. If you are appending more than two tables click the Three or more tables radio button
7. Click OK

8. At this point you may get the Information is required about data privacy warning. We are getting this warning as we are appending the data from two data sources which can potentially lead to data leakage as the data can trasit from a data source to another. In our scenario this is not a concern as we are the owner of both data sources. Click the Continue button

9. Set the privacy level for all appending data sources
10. Click Save

Important note:

I set the Privacy Level to Organisational as I am not dealing with sensitive data in this particular example. Ensure you completely understand the different options available for configuring the Privacy Level to prevent any potential data leakage. Read more about Privacy Levels here.

11. Select the Append1 query from the Queries pane and rename it to Tasks. You can rename a query by double clicking the query from the Queries pane.

Now that we appended the queries, it is good to look at the results to see how many of the requirements we can meet with the data in its current shape.

I can see in the data that I have a Folder Path column. This column shows me the different task lists I created in Microsoft To Do. The following image shows the data in Power Query Editor side-by-side the Microsoft To Do tasks from one of my accounts.

Revisiting the Requirements

Looking closer to the data shows that the Subject column contains my tasks; I have StartDate and DueDate columns as well as Status and Importance columns. Looking at my requirements, I can use all of those columns to support my requirements coloured in green:

• Today’s tasks
  • Number of tasks
  • Number of important tasks
  • Tasks by mailbox
  • Tasks details
    • Task list
    • Task description
    • Status
    • Start date
    • Due date
    • A link to the task itself that I can update if I want to
• All Tasks
  • All above plus
    • Number of open tasks
    • Number of completed tasks

As you can see, we can already meet many requirements, but what about the requirements coloured in purple? If we look at the data, we see no way to distinguish between tasks coming from multiple accounts. This is an issue that we have to solve. One quick fix is to add a new Custom Column into both Task 1 and Task 2 queries using the values of the two query parameters. Remember, the query parameters contain our email accounts. As stated earlier, we disabled data load and appended the Task 1 and Task 2 queries as a new query. The Tasks query must include the new custom column.

1. Click the Tasks 1 query from the Queries pane
2. Click the Custom Column button from the Add Column tab
3. Give the new column a name
4. Type the corresponding query parameter name used for connecting to Microsoft Exchange Online
5. Click OK

If we scroll to the very end (to the right), we see our email address is added as a new column.

Now repeat the above steps (1 to 5) for the Tasks 2 query.

6. Click the Tasks query from the Queries pane
7. On the data view, scroll right to the very end to see the new Source column presents

With that, we are now cable of identifying tasks by the mailbox.

So the only remaining requirement that the current data does not support is a link to the task to modify the task if needed. This one is a bit tricky as the data doesn’t suggest such a link at the first look. Let’s look more thoroughly at the Tasks data. There are 5 columns containing structured values within the Tasks table. We can click on each cell of data to see the underlying data.

Note:

If we click on the structured value itself, Power Query drills down to the underlying data. We need to click the cell, not the value.

Manually exploring all structured columns to find a specific value can be a time-consuming process. We can use some techniques to go through the structured values, but those are out of the scope of this post. Maybe I write a separate blog post about that later. But the good news is that I know which column contains the URL to the Task. It is the Attributes column. Follow these steps to expand the Attributes column and the URL:

8. Click the Expand button on the right side of the Attributes column
9. Untick the Select All Columns
10. Untick the Use original column name as prefix option
11. Scroll down and tick the WebClientReadFormQueryString
12. Click OK

13. Double click the WebClientReadFormQueryString column and rename it to Web URL
14. Click the Close & Apply button from the Home tab

So far, we prepared the data to support all our requirements. When we click the Close & Apply button on Power Query Editor, it loads the data for all queries in which their Enable Load is active. We disabled data load for Tasks 1 and Tasks 2 tables. Therefore only the data of the Tasks table is loaded into the data model.

Data Modelling

In the previous section of this blog post, we prepared the data based on our requirements. The data is now loaded into the data model. If we look at the requirements again, we see some points that we have to take care of. Our requirements have two main parts to analyse the data for Today’s Tasks and All Tasks. To analyse Today’s Tasks, we require to identify if the DueDate falls into today’s date. To be able to tackle date-related calculations, we require to create a Date table. Read more about the Date table here.

Creating Date Table

We can create the Date table either in Power Query Editor or in the data model using DAX. In my sample, I create the Date table using DAX. Other bloggers over the internet already wrote DAX expressions to create a Date table. Some are very simple, and some are very complex. I use a straightforward version using the CALENDARAUTO() function.

1. Click the New table button from the Modelling tab
2. Copy/paste the following DAX expression and press Enter

Date = 
    ADDCOLUMNS(
        CALENDARAUTO() 
        , "Year", YEAR([Date])
        , "Month", FORMAT([Date], "MMMM")
        , "MonthOrder", FORMAT([Date], "MM")
        , "Day", DAY([Date])
        , "IsToday", [Date] = TODAY()
        )

Marking Date Table as Date

So far, we created a Date table. Now we have to mark it as Date. Read more here to understand what marking a Date table as Date means and why we should do that. Follow these steps to do so:

1. Right click the Date table
2. Hover over Mark as date table and click the Mark as date table from the context menu
3. Select the Date as Date column. Make sure the validation was successful
4. Click OK

Creating Relationships

Now that we successfully created the Date table and marked it as Date, we must create the relationship between the Tasks and the Date table. Learn more about the concept behind the relationships here. We have a few options when it comes to create and manage relationships:

• We can use the Manage Relationships button available on the Report view, Data view and Model view (shown in the following image)
• Or we can drag-and-drop key columns from a table to another

The following steps show how to create the relationship from the Model view using the drag-and-drop option:

1. Click the Model view
2. Drag the Date column from the Date table and drop it on the StartDate column from the Tasks table. This creates an Active Relationship showed with a solid line between the Date and Tasks tables
3. Drag the Date column from the Date table, but this time, drop it on the DueDate column from the Tasks table. This creates an Inactive Relationship between the two tables

Read more about Active vs. Inactive relationships here.

By creating the relationships, we can now support all our requirements. Our date table has an IsToday column that flags if a specific date is indeed today’s date. We can use this column later to answer the questions around Today’s Tasks. With that, it is time to create some measures.

Creating Measures

One of the most common and undoubtedly the most important tasks while developing a Power BI data model is creating measures. With measures, we can do calculations such as summations, averages, counts, etc. There are two types of measures:

• Implicit measures: Implicit measures or automatic measures shown with a Sigma icon () in the Fields pane in Power BI Desktop. These are the measures that are automatically created when used in a visual on the reporting canvas. In other words, we do not create implicit measures.
• Explicit measures: Explicit measures on the other hand are those ones we create within the data model using DAX. The explicit measures also show up in the Fields pane in Power BI Desktop. The icon for explicit measures is a calculator ().

Read more about measures here.

It is best practice always to create explicit measures. When I mention creating a measure, I refer to explicit measures. We must always create the measures to support our requirements, so in our sample, we will create the following measures:

• Measures to calculate for all tasks
  • Number of tasks
  • Number of important tasks
  • Number of open tasks
  • Number of completed tasks
• Measures to calculate for today’s tasks:
  • Number of tasks
  • Number of important tasks

Here is how we create a measure in Power BI Desktop:

1. From the Report view or the Data view, right-click the Tasks table
2. Click the New measure
3. Type in the following DAX expression
4. Press Enter from the keyboard or click the Submit button to create the Number of Tasks measure

Number of Tasks = COUNTROWS(Tasks)

Repeat the above process for the other measures using the DAX expressions below.

Measures for All Tasks

Important Tasks = 
    CALCULATE([Number of Tasks]
        , Tasks[Importance] = "High"
    )

Open Tasks = 
    CALCULATE([Number of Tasks]
        , NOT(Tasks[IsComplete])
    )

Completed Tasks = 
    CALCULATE([Number of Tasks]
        , Tasks[IsComplete]
    )

Measures for Today’s Tasks

Per our requirements for calculating Today’s Tasks, we have to find all tasks that their StartDate or DueDate is today or the Tasks with no StartDate and DueDate. This specific part of the requirement contains three conditions:

• Tasks starting today
• Tasks due today
• Tasks without StartDate and DueDate

So we can break the calculation into three separate measures. We then create a fourth measure to add up the results of those three measures as below:

Tasks Starting Today = 
    CALCULATE([Open Tasks]
        , 'Date'[IsToday]
        )

Tasks Due Today = 
    CALCULATE([Open Tasks]
        , 'Date'[IsToday]
        , USERELATIONSHIP('Date'[Date], Tasks[DueDate])
        )

Tasks with No Start or Due Date = 
    CALCULATE([Open Tasks]
        , AND(ISBLANK(Tasks[StartDate]), ISBLANK(Tasks[DueDate]))
        )

Today's Tasks = [Tasks Starting Today] + [Tasks Due Today] + [Tasks with No Start or Due Date]

The last measure to create is Today’s Important Tasks. The following DAX expression caters that:

Today's Important Tasks = 
    CALCULATE([Today's Tasks]
      , Tasks[Importance] = "High"
    )

Now that we created all the required measures, it is time to visualise the data.

Data Visualisation

So far, we prepared the data and built our data model. It is time now to bring our data to life and built some meaningful data visualisation. Again, the general rule of thumb is to look at our requirements first then start visualising the data. For your convenience, I copy the requirements here to avoid moving up and down in this blog post.

• Today’s tasks: All tasks that their StartDate or DueDate is today or the Tasks without any StartDate and DueDate
  • Number of tasks
  • Number of important tasks
  • Tasks by mailbox
  • Tasks details
    • Task list
    • Task description
    • Status
    • Start date
    • Due date
    • A link to the task itself that I can update if I want to
• All Tasks
  • All above plus
    • Number of open tasks
    • Number of completed tasks

One of the biggest challenges in data visualisation, regardless of the visualisation tool we use, is real estate. It is quite challenging to use the available space on the report canvas to represent the information efficiently. It isn’t worth building a flashy and colourful report that doesn’t tell a story about the data and does not answer the business questions reflected in the requirements. Data visualisation is a major topic that requires special attention to detail. There are many best practices around data visualisation that are out of the scope of this blog post. So, I put together a simple data visualisation that meets all the requirements.

In the next few sections, I quickly explain some simple techniques used in the above visualisation, leaving the rest for you to investigate.

Showing URL Links in Table Visual

Showing a URL link instead of showing a textual full link is very easy. Follow these steps to get it done:

1. Select the Web URL column from the Tasks table
2. Select the Web URL option from the Data category dropdown from the Column tools tab
3. Click the Table visual
4. Click the Format tab from the Visualisations pane
5. Search for url
6. Toggle on the URL icon option

Filtering the Data to Show Today’s Tasks Only

As you can see in the Today’s Tasks report page, we have a Table visual containing the required columns from the Tasks table showing all tasks. We require to filter the data shown on the table only to show today’s tasks. You may think that we can use the IsToday column from the Date table on the visual filters. Suppose we filter the Table visual using the IsToday column when IsToday equals True. In that case, we are putting a filter on the Date table. The filter propagates to the Tasks table via the relationship between the Date column from the Date table and the StartDate column from the Tasks table. Therefore, the values on the Table visual are filtered only when the start date is today. But this is not what we require. The requirements clearly say, “Today’s tasks are all tasks that their StartDate or DueDate is today or the Tasks without any StartDate and DueDate”. So we cannot simply filter the values of the Table visual by the IsToday column from the Date table. The solution is simple. We already implemented the required logic in the Today's Tasks measure. We can put this measure on the Table visual, which results in showing the correct values.

While it may look to be a good solution, it is not ideal. In reality, we are not adding any insights by adding the Today's Tasks measure to the Table visual, as it shows 1 in almost all rows. Adding a measure to a visual to solely solve our problem without adding any value is inappropriate. A better solution is to filter the Table visual by the Today's Tasks measure. Follow these steps to see how:

1. On the Today’s Tasks page, click the Table visual
2. Right click the Today's Tasks measure from the Fields pane
3. Hover over Add to filters and click Visual-level filters

4. On the Filters pane, select the is not blank option from the Show items when the value dropdown list
5. Click Apply filter

It is done now.

Downloading the Sample File

You can download the PBIT version of the report from here.

As always, please let me know if you have any comments or feedback via the comments section below.