Integrating and Visualising A number of Microsoft To Do Accounts with Energy BI

It’s been some time that I take advantage of Microsoft To Do to organise my each day duties. From work-related duties to purchase groceries. Whereas Microsoft To Do is tremendous simple to make use of however there are some challenges in utilizing it extra effectively, particularly when you’ve got a number of O365 accounts inside totally different organisations. Listed here are a few of the challenges I confronted; chances are you’ll face different challenges too:

• The Microsoft To Do app for Home windows units could be very consumer pleasant with amazingly good options like the power so as to add a number of To Do accounts. Nonetheless, we at the moment have to pick out which account we want to use and the app reveals all our duties inside that particular account. This implies we can’t see all our duties from all our accounts in a single place.
• The Microsoft To Do app for iOS units can also be very helpful to make use of, however it lacks including a number of accounts. Therefore we can’t see all our duties from a number of O365 accounts on the app. 🙁
• We are able to use the Duties inside the Microsoft Outlook desktop software (I used the Home windows model) which is by far probably the most complete one with tons of options. Whereas we will see duties from a number of accounts in a single place, it’s a actual problem if I wish to know which activity is assigned to which account. Apart from, it’s actually onerous to reply some questions like, what number of high-priority duties I’ve for at this time or the week forward. I do know, we will group duties, however, it’s nonetheless not so intuitive.

For the above causes, I looked for a product that may do all of the above without delay. After spending some hours, I believed, effectively, I’ve to do it myself.

With that, let’s go forward and see how we will get the job executed in Energy BI.

Notice:

This methodology just isn’t working for Microsoft To Do utilizing private accounts corresponding to Outlook, Hotmail or MSN. If anybody is aware of find out how to add these, please tell us within the feedback part under this put up.

It is a lengthy put up that took me an inexpensive period of time to put in writing. So I added the next desk of contents so you may shortly bounce to a topic of your curiosity.

Desk of Contents

How It Works

Microsoft Energy BI is NOT a reporting software solely. We are able to hook up with many knowledge sources, combine and match the information, create knowledge fashions and visualise the information. So it must be potential to hook up with a number of To Do accounts, append the information, create a easy knowledge mannequin on prime of that, and visualise the information to reply our questions or our clients’ questions. The Microsoft To Do knowledge is accessible through the Microsoft Alternate On-line connector accessible in Energy BI. The remaining is dependent upon our necessities and what questions we want to reply.

In my case, during which I’m the end-user of the report, I would really like to have the ability to know:

• At this time’s duties: All duties that their StartDate or DueDate is at this time or the Duties with none StartDate and DueDate
  • Variety of duties
  • Variety of necessary duties
  • Duties by mailbox
  • Duties particulars
    • Process checklist
    • Process description
    • Standing
    • Begin date
    • Due date
    • A hyperlink to the duty itself that I can replace if I wish to
• All Duties
  • All above plus
    • Variety of open duties
    • Variety of accomplished duties

You or your buyer(s) might need totally different necessities, however when you perceive find out how to get the To Do knowledge from Microsoft Alternate On-line and do some knowledge explorations to search out out what you might be after, you’ll be good.

Integrating A number of To Do Accounts in Energy BI

Earlier than we begin, I prefer to encourage you to alter your Energy BI settings to permit parameterisation in all connection and transformation dialogue bins. The next steps clarify how to take action:

1. In Energy BI Desktop click on the File menu
2. Click on Choices and settings
3. Click on Choices
4. Within the Choices, window choose the Energy Question Editor tab
5. Tick the All the time enable parameterization in knowledge supply and transformation dialogs choice
6. Click on OK

With that, allow us to get the Microsoft To Do knowledge in Energy BI Desktop.

Getting Information from the Supply

This part explains find out how to get the information from Microsoft Alternate On-line for Microsoft To Do.

Comply with these steps:

1. Click on Get knowledge in Energy BI Desktop
2. Seek for Microsoft Alternate
3. Choose Microsoft Alternate On-line (you should use Microsoft Alternate as effectively, in our situation each connections work the identical)
4. Click on Join

5. Click on the Parameterise drop down button and choose the New parameter… choice to open the Magane Parameters window. This button is enabled as we beforehand enabled this characteristic.

6. Kind in a Identify for the parameter
7. Change the Kind to Textual content
8. Kind in your e-mail deal with related together with your account within the Present Worth
9. Click on New and repeat the method from step 6 for creating a question parameter for all To Do accounts you’d prefer to combine
10. Click on OK

11. Choose the specified question parameter within the Mailbox deal with dropdown from the Microsoft Alternate connector
12. Click on OK

We’ve got a couple of choices on the Navigator window. We are able to analyse our Calendar, E mail, and so on., however this weblog put up goals to get the information from Microsoft To Do. I depart that so that you can discover extra on these areas :).

13. Tick the Duties desk. That is the desk that retains all To Do knowledge 😀
14. Click on Remodel Information

We are actually navigated to the Energy Question Editor with 3 queries showing on the Queries pane; the 2 question parameters we created earlier and the Process desk loaded from Microsoft Alternate On-line. Now we have now to repeat the method and get the information from our different accounts. After getting knowledge from all our accounts, we have now to arrange the information to fulfill our necessities.

Notice:

I renamed the queries to Process 1 and Process 2.

Thus far, we linked to a number of Microsoft Alternate On-line sources. That’s the very first step. The subsequent step is to arrange the information to fulfill our necessities.

Information Preparation

I’ve to say that the information is effectively structured already; due to this fact, we have now to spend much less effort to arrange the information. The following couple of steps deal with my necessities which is likely to be totally different than yours:

1. Proper-click every question
2. Disable knowledge load (I want I may choose a number of queries and disable load for all chosen queries. When you suppose this can be a good concept that improves the event please vote for this concept which was posted a very long time in the past.)

I disabled the information load as I require to Append the queries. I choose to append the queries right into a separate question, so it could be simpler to make modifications sooner or later if I require to alter something within the unique queries. I’ll revisit this level later on this put up.

3. Choose a question from the Queries pane
4. Click on the Append Queries dropdown button
5. From the House tab, choose the Append Queries as New choice

6. Choose the primary and second queries you’d prefer to append. In case you are appending greater than two tables click on the Three or extra tables radio button
7. Click on OK

8. At this level chances are you’ll get the Data is required about knowledge privateness warning. We’re getting this warning as we’re appending the information from two knowledge sources which might doubtlessly result in knowledge leakage as the information can trasit from a knowledge supply to a different. In our situation this isn’t a priority as we’re the proprietor of each knowledge sources. Click on the Proceed button

9. Set the privateness stage for all appending knowledge sources
10. Click on Save

Vital be aware:

I set the Privateness Stage to Organisational as I’m not coping with delicate knowledge on this explicit instance. Make sure you utterly perceive the totally different choices accessible for configuring the Privateness Stage to forestall any potential knowledge leakage. Learn extra about Privateness Ranges right here.

11. Choose the Append1 question from the Queries pane and rename it to Duties. You possibly can rename a question by double clicking the question from the Queries pane.

Now that we appended the queries, it’s good to have a look at the outcomes to see how most of the necessities we will meet with the information in its present form.

I can see within the knowledge that I’ve a Folder Path column. This column reveals me the totally different activity lists I created in Microsoft To Do. The next picture reveals the information in Energy Question Editor side-by-side the Microsoft To Do duties from one among my accounts.

Revisiting the Necessities

Trying nearer to the information reveals that the Topic column comprises my duties; I’ve StartDate and DueDate columns in addition to Standing and Significance columns. my necessities, I can use all of these columns to assist my necessities colored in inexperienced:

• At this time’s duties
  • Variety of duties
  • Variety of necessary duties
  • Duties by mailbox
  • Duties particulars
    • Process checklist
    • Process description
    • Standing
    • Begin date
    • Due date
    • A hyperlink to the duty itself that I can replace if I wish to
• All Duties
  • All above plus
    • Variety of open duties
    • Variety of accomplished duties

As you may see, we will already meet many necessities, however what concerning the necessities colored in purple? If we take a look at the information, we see no strategy to distinguish between duties coming from a number of accounts. This is a matter that we have now to resolve. One fast repair is so as to add a brand new Customized Column into each Process 1 and Process 2 queries utilizing the values of the 2 question parameters. Keep in mind, the question parameters comprise our e-mail accounts. As said earlier, we disabled knowledge load and appended the Process 1 and Process 2 queries as a brand new question. The Duties question should embody the brand new customized column.

1. Click on the Duties 1 question from the Queries pane
2. Click on the Customized Column button from the Add Column tab
3. Give the brand new column a reputation
4. Kind the corresponding question parameter title used for connecting to Microsoft Alternate On-line
5. Click on OK

If we scroll to the very finish (to the proper), we see our e-mail deal with is added as a brand new column.

Now repeat the above steps (1 to five) for the Duties 2 question.

6. Click on the Duties question from the Queries pane
7. On the information view, scroll proper to the very finish to see the brand new Supply column presents

With that, we are actually cable of figuring out duties by the mailbox.

So the one remaining requirement that the present knowledge doesn’t assist is a hyperlink to the duty to switch the duty if wanted. This one is a bit tough as the information doesn’t counsel such a hyperlink on the first look. Let’s look extra completely on the Duties knowledge. There are 5 columns containing structured values inside the Duties desk. We are able to click on on every cell of information to see the underlying knowledge.

Notice:

If we click on on the structured worth itself, Energy Question drills all the way down to the underlying knowledge. We have to click on the cell, not the worth.

Manually exploring all structured columns to discover a particular worth could be a time-consuming course of. We are able to use some strategies to undergo the structured values, however these are out of the scope of this put up. Perhaps I write a separate weblog put up about that later. However the excellent news is that I do know which column comprises the URL to the Process. It’s the Attributes column. Comply with these steps to increase the Attributes column and the URL:

8. Click on the Broaden button on the proper facet of the Attributes column
9. Untick the Choose All Columns
10. Untick the Use unique column title as prefix choice
11. Scroll down and tick the WebClientReadFormQueryString
12. Click on OK

13. Double click on the WebClientReadFormQueryString column and rename it to Net URL
14. Click on the Shut & Apply button from the House tab

Thus far, we ready the information to assist all our necessities. After we click on the Shut & Apply button on Energy Question Editor, it masses the information for all queries during which their Allow Load is energetic. We disabled knowledge load for Duties 1 and Duties 2 tables. Subsequently solely the information of the Duties desk is loaded into the information mannequin.

Information Modelling

Within the earlier part of this weblog put up, we ready the information based mostly on our necessities. The info is now loaded into the information mannequin. If we take a look at the necessities once more, we see some factors that we have now to deal with. Our necessities have two primary components to analyse the information for At this time’s Duties and All Duties. To analyse At this time’s Duties, we require to determine if the DueDate falls into at this time’s date. To have the ability to deal with date-related calculations, we require to create a Date desk. Learn extra concerning the Date desk right here.

Creating Date Desk

We are able to create the Date desk both in Energy Question Editor or within the knowledge mannequin utilizing DAX. In my pattern, I create the Date desk utilizing DAX. Different bloggers over the web already wrote DAX expressions to create a Date desk. Some are quite simple, and a few are very complicated. I take advantage of a simple model utilizing the CALENDARAUTO() perform.

1. Click on the New desk button from the Modelling tab
2. Copy/paste the next DAX expression and press Enter

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

Marking Date Desk as Date

Thus far, we created a Date desk. Now we have now to mark it as Date. Learn extra right here to grasp what marking a Date desk as Date means and why we should always try this. Comply with these steps to take action:

1. Proper click on the Date desk
2. Hover over Mark as date desk and click on the Mark as date desk from the context menu
3. Choose the Date as Date column. Make certain the validation was profitable
4. Click on OK

Creating Relationships

Now that we efficiently created the Date desk and marked it as Date, we should create the connection between the Duties and the Date desk. Be taught extra concerning the idea behind the relationships right here. We’ve got a couple of choices in relation to create and handle relationships:

• We are able to use the Handle Relationships button accessible on the Report view, Information view and Mannequin view (proven within the following picture)
• Or we will drag-and-drop key columns from a desk to a different

The next steps present find out how to create the connection from the Mannequin view utilizing the drag-and-drop choice:

1. Click on the Mannequin view
2. Drag the Date column from the Date desk and drop it on the StartDate column from the Duties desk. This creates an Lively Relationship confirmed with a strong line between the Date and Duties tables
3. Drag the Date column from the Date desk, however this time, drop it on the DueDate column from the Duties desk. This creates an Inactive Relationship between the 2 tables

Learn extra about Lively vs. Inactive relationships right here.

By creating the relationships, we will now assist all our necessities. Our date desk has an IsToday column that flags if a selected date is certainly at this time’s date. We are able to use this column later to reply the questions round At this time’s Duties. With that, it’s time to create some measures.

Creating Measures

Probably the most frequent and undoubtedly an important duties whereas creating a Energy BI knowledge mannequin is creating measures. With measures, we will do calculations corresponding to summations, averages, counts, and so on. There are two sorts of measures:

• Implicit measures: Implicit measures or computerized measures proven with a Sigma icon () within the Fields pane in Energy BI Desktop. These are the measures which can be routinely created when utilized in a visible on the reporting canvas. In different phrases, we do not create implicit measures.
• Express measures: Express measures then again are these ones we create inside the knowledge mannequin utilizing DAX. The express measures additionally present up within the Fields pane in Energy BI Desktop. The icon for express measures is a calculator ().

Learn extra about measures right here.

It’s best observe all the time to create express measures. Once I point out making a measure, I check with express measures. We should all the time create the measures to assist our necessities, so in our pattern, we are going to create the next measures:

• Measures to calculate for all duties
  • Variety of duties
  • Variety of necessary duties
  • Variety of open duties
  • Variety of accomplished duties
• Measures to calculate for at this time’s duties:
  • Variety of duties
  • Variety of necessary duties

Right here is how we create a measure in Energy BI Desktop:

1. From the Report view or the Information view, right-click the Duties desk
2. Click on the New measure
3. Kind within the following DAX expression
4. Press Enter from the keyboard or click on the Submit button to create the Variety of Duties measure

Variety of Duties = COUNTROWS(Duties)

Repeat the above course of for the opposite measures utilizing the DAX expressions under.

Measures for All Duties

Vital Duties = 
    CALCULATE([Number of Tasks]
        , Duties[Importance] = "Excessive"
    )

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

Accomplished Duties = 
    CALCULATE([Number of Tasks]
        , Duties[IsComplete]
    )

Measures for At this time’s Duties

Per our necessities for calculating At this time’s Duties, we have now to search out all duties that their StartDate or DueDate is at this time or the Duties with no StartDate and DueDate. This particular a part of the requirement comprises three situations:

• Duties beginning at this time
• Duties due at this time
• Duties with out StartDate and DueDate

So we will break the calculation into three separate measures. We then create a fourth measure so as to add up the outcomes of these three measures as under:

Duties Beginning At this time = 
    CALCULATE([Open Tasks]
        , 'Date'[IsToday]
        )

Duties Due At this time = 
    CALCULATE([Open Tasks]
        , 'Date'[IsToday]
        , USERELATIONSHIP('Date'[Date], Duties[DueDate])
        )

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

At this time's Duties = [Tasks Starting Today] + [Tasks Due Today] + [Tasks with No Start or Due Date]

The final measure to create is At this time’s Vital Duties. The next DAX expression caters that:

At this time's Vital Duties = 
    CALCULATE([Today's Tasks]
      , Duties[Importance] = "Excessive"
    )

Now that we created all of the required measures, it’s time to visualise the information.

Information Visualisation

Thus far, we ready the information and constructed our knowledge mannequin. It’s time now to carry our knowledge to life and constructed some significant knowledge visualisation. Once more, the overall rule of thumb is to have a look at our necessities first then begin visualising the information. To your comfort, I copy the necessities right here to keep away from shifting up and down on this weblog put up.

• At this time’s duties: All duties that their StartDate or DueDate is at this time or the Duties with none StartDate and DueDate
  • Variety of duties
  • Variety of necessary duties
  • Duties by mailbox
  • Duties particulars
    • Process checklist
    • Process description
    • Standing
    • Begin date
    • Due date
    • A hyperlink to the duty itself that I can replace if I wish to
• All Duties
  • All above plus
    • Variety of open duties
    • Variety of accomplished duties

One of many greatest challenges in knowledge visualisation, whatever the visualisation software we use, is actual property. It’s fairly difficult to make use of the accessible house on the report canvas to signify the knowledge effectively. It isn’t price constructing a flashy and vibrant report that doesn’t inform a narrative concerning the knowledge and doesn’t reply the enterprise questions mirrored within the necessities. Information visualisation is a significant matter that requires particular consideration to element. There are various greatest practices round knowledge visualisation which can be out of the scope of this weblog put up. So, I put collectively a easy knowledge visualisation that meets all the necessities.

Within the subsequent few sections, I shortly clarify some easy strategies used within the above visualisation, leaving the remaining so that you can examine.

Displaying URL Hyperlinks in Desk Visible

Displaying a URL hyperlink as an alternative of exhibiting a textual full hyperlink could be very simple. Comply with these steps to get it executed:

1. Choose the Net URL column from the Duties desk
2. Choose the Net URL choice from the Information class dropdown from the Column instruments tab
3. Click on the Desk visible
4. Click on the Format tab from the Visualisations pane
5. Seek for url
6. Toggle on the URL icon choice

Filtering the Information to Present At this time’s Duties Solely

As you may see within the At this time’s Duties report web page, we have now a Desk visible containing the required columns from the Duties desk exhibiting all duties. We require to filter the information proven on the desk solely to point out at this time’s duties. It’s possible you’ll suppose that we will use the IsToday column from the Date desk on the visible filters. Suppose we filter the Desk visible utilizing the IsToday column when IsToday equals True. In that case, we’re placing a filter on the Date desk. The filter propagates to the Duties desk through the connection between the Date column from the Date desk and the StartDate column from the Duties desk. Subsequently, the values on the Desk visible are filtered solely when the beginning date is at this time. However this isn’t what we require. The necessities clearly say, “At this time’s duties are all duties that their StartDate or DueDate is at this time or the Duties with none StartDate and DueDate”. So we can’t merely filter the values of the Desk visible by the IsToday column from the Date desk. The answer is easy. We already carried out the required logic within the At this time's Duties measure. We are able to put this measure on the Desk visible, which ends up in exhibiting the proper values.

Whereas it might look to be resolution, it’s not superb. In actuality, we aren’t including any insights by including the At this time's Duties measure to the Desk visible, because it reveals 1 in virtually all rows. Including a measure to a visible to solely resolve our drawback with out including any worth is inappropriate. A greater resolution is to filter the Desk visible by the At this time's Duties measure. Comply with these steps to see how:

1. On the At this time’s Duties web page, click on the Desk visible
2. Proper click on the At this time's Duties measure from the Fields pane
3. Hover over Add to filters and click on Visible-level filters

4. On the Filters pane, choose the just isn’t clean choice from the Present objects when the worth dropdown checklist
5. Click on Apply filter

It’s executed now.

Downloading the Pattern File

You possibly can obtain the PBIT model of the report from right here.

As all the time, please let me know in case you have any feedback or suggestions through the feedback part under.

Associated