Wednesday, January 24, 2024
HomeBusiness IntelligenceIntegrating and Visualising A number of Microsoft To Do Accounts with Energy...

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


Integrating and Visualising Multiple Microsoft To Do Accounts with Power BI

It’s been some time that I take advantage of Microsoft To Do to organise my every 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 have got a number of O365 accounts inside completely different organisations. Listed below are among the challenges I confronted; you could face different challenges too:

  • The Microsoft To Do app for Home windows gadgets could be very person 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 which account we wish to use and the app exhibits 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 gadgets can also be very helpful to make use of, nevertheless it lacks including a number of accounts. Therefore we can not 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 essentially the most complete one with tons of options. Whereas we are able to see duties from a number of accounts in a single place, it’s a actual problem if I need to know which job is assigned to which account. Apart from, it’s actually laborious to reply some questions like, what number of high-priority duties I’ve for at the moment or the week forward. I do know, we are able to 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 assumed, effectively, I’ve to do it myself.

With that, let’s go forward and see how we are able to get the job completed in Energy BI.

Observe:

This technique 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 beneath this submit.

This can be a lengthy submit that took me an affordable period of time to jot down. So I added the next desk of contents so you may rapidly bounce to a topic of your curiosity.

Desk of Contents

How It Works

Microsoft Energy BI is NOT a reporting instrument solely. We are able to connect with many knowledge sources, combine and match the info, create knowledge fashions and visualise the info. So it needs to be doable to hook up with a number of To Do accounts, append the info, create a easy knowledge mannequin on prime of that, and visualise the info to reply our questions or our clients’ questions. The Microsoft To Do knowledge is accessible by way of the Microsoft Trade On-line connector out there in Energy BI. The remainder is determined by our necessities and what questions we wish to reply.

In my case, wherein I’m the end-user of the report, I would love to have the ability to know:

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

You or your buyer(s) may need completely different necessities, however when you perceive find out how to get the To Do knowledge from Microsoft Trade On-line and do some knowledge explorations to seek out out what you’re 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 containers. 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 possibility
  6. Click on OK
Enabling the "Always allow parameterization in data source and transformation dialogs" feature in Power BI Desktop
Enabling the “All the time enable parameterization within the knowledge supply and transformation dialogs” characteristic in Energy BI Desktop

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

Getting Knowledge from the Supply

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

Observe these steps:

  1. Click on Get knowledge in Energy BI Desktop
  2. Seek for Microsoft Trade
  3. Choose Microsoft Trade On-line (you should use Microsoft Trade as effectively, in our situation each connections work the identical)
  4. Click on Join
Power Query connection to Microsoft Exchange
Energy Question connection to Microsoft Trade
  1. 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.
  1. Kind in a Title for the parameter
  2. Change the Kind to Textual content
  3. Kind in your e-mail deal with related along with your account within the Present Worth
  4. 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
  5. Click on OK
Defining Query Parameters in Power BI Desktop
Defining Question Parameters in Energy BI Desktop
  1. Choose the specified question parameter within the Mailbox deal with dropdown from the Microsoft Trade connector
  2. Click on OK
Selecting a Query Parameter in a Connection Dialog box in Power BI Desktop
Choosing a Question Parameter in a Connection Dialog field in Energy BI Desktop

We’ve got just a few choices on the Navigator window. We are able to analyse our Calendar, E mail, and many others., however this weblog submit goals to get the info from Microsoft To Do. I go away that so that you can discover extra on these areas :).

  1. Tick the Duties desk. That is the desk that retains all To Do knowledge 😀
  2. Click on Rework Knowledge
Getting To Do data in Power Query
Getting To Do knowledge in Energy Question

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

Observe:

I renamed the queries to Activity 1 and Activity 2.

Power Query Connected to Multiple Microsoft Exchange Online Instances
Energy Question Related to A number of Microsoft Trade On-line Cases

Up to now, we linked to a number of Microsoft Trade On-line sources. That’s the very first step. The subsequent step is to arrange the info to fulfill our necessities.

Knowledge Preparation

I’ve to say that the info is effectively structured already; subsequently, we have now to spend much less effort to arrange the info. The next few steps maintain my necessities which could be completely 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. For those who assume 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.)
Disabling Query Data Load in Power Query
Disabling Question Knowledge Load in Energy Question

I disabled the info load as I require to Append the queries. I choose to append the queries right into a separate question, so it might 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 submit.

  1. Choose a question from the Queries pane
  2. Click on the Append Queries dropdown button
  3. From the Dwelling tab, choose the Append Queries as New possibility
Appending Queries as a New Query in Power Query
Appending Queries as a New Question in Energy Question
  1. 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
  2. Click on OK
Appending two or more queries
Appending two or extra queries
  1. At this level you could get the Data is required about knowledge privateness warning. We’re getting this warning as we’re appending the info from two knowledge sources which might doubtlessly result in knowledge leakage as the info can trasit from an information 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
Data privacy warning in Power Query
Knowledge privateness warning in Energy Question
  1. Set the privateness degree for all appending knowledge sources
  2. Click on Save
Configuring the Privacy Level for Queries in Power Query
Configuring the Privateness Degree for Queries in Energy Question

Necessary notice:

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

  1. Choose the Append1 question from the Queries pane and rename it to Duties. You may rename a question by double clicking the question from the Queries pane.
Renaming a Query in Power Query
Renaming a Question in Energy Question

Now that we appended the queries, it’s good to take a look at the outcomes to see how lots of the necessities we are able to meet with the info in its present form.

I can see within the knowledge that I’ve a Folder Path column. This column exhibits me the completely different job lists I created in Microsoft To Do. The next picture exhibits the info in Energy Question Editor side-by-side the Microsoft To Do duties from one in all my accounts.

Comparing To Do Tasks with Extracted Data from Microsoft Exchange Online
Evaluating To Do Duties with Extracted Knowledge from Microsoft Trade On-line

Revisiting the Necessities

Trying nearer to the info exhibits that the Topic column comprises my duties; I’ve StartDate and DueDate columns in addition to Standing and Significance columns. Taking a look at my necessities, I can use all of these columns to help my necessities colored in inexperienced:

  • At the moment’s duties
    • Variety of duties
    • Variety of essential duties
    • Duties by mailbox
    • Duties particulars
      • Activity checklist
      • Activity description
      • Standing
      • Begin date
      • Due date
      • A hyperlink to the duty itself that I can replace if I need to
  • All Duties
    • All above plus
      • Variety of open duties
      • Variety of accomplished duties

As you may see, we are able to already meet many necessities, however what in regards to the necessities colored in purple? If we take a look at the info, we see no approach to distinguish between duties coming from a number of accounts. This is a matter that we have now to unravel. One fast repair is so as to add a brand new Customized Column into each Activity 1 and Activity 2 queries utilizing the values of the 2 question parameters. Bear in mind, the question parameters include our e-mail accounts. As acknowledged earlier, we disabled knowledge load and appended the Activity 1 and Activity 2 queries as a brand new question. The Duties question should embrace 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 Trade On-line
  5. Click on OK
Adding Source as a New Custom Column in Power Query
Including Supply as a New Customized Column in Energy Question

If we scroll to the very finish (to the correct), 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.

  1. Click on the Duties question from the Queries pane
  2. On the info view, scroll proper to the very finish to see the brand new Supply column presents
Checking the Source Column Exists in the Appended Query
Checking the Supply Column Exists within the Appended Question

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

So the one remaining requirement that the present knowledge doesn’t help is a hyperlink to the duty to change the duty if wanted. This one is a bit tough as the info doesn’t counsel such a hyperlink on the first look. Let’s look extra totally 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 knowledge to see the underlying knowledge.

The Underlying Data of an Structured Value
The Underlying Knowledge of a Structured Worth

Observe:

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 methods to undergo the structured values, however these are out of the scope of this submit. Possibly I write a separate weblog submit about that later. However the excellent news is that I do know which column comprises the URL to the Activity. It’s the Attributes column. Observe these steps to develop the Attributes column and the URL:

  1. Click on the Broaden button on the correct facet of the Attributes column
  2. Untick the Choose All Columns
  3. Untick the Use unique column title as prefix possibility
  4. Scroll down and tick the WebClientReadFormQueryString
  5. Click on OK
Expanding Structured Columns in Power Query
Increasing Structured Columns in Energy Question
  1. Double click on the WebClientReadFormQueryString column and rename it to Internet URL
  2. Click on the Shut & Apply button from the Dwelling tab
Renaming Columns in Power Query
Renaming Columns in Energy Question

Up to now, we ready the info to help all our necessities. Once we click on the Shut & Apply button on Energy Question Editor, it hundreds the info for all queries wherein their Allow Load is energetic. We disabled knowledge load for Duties 1 and Duties 2 tables. Due to this fact solely the info of the Duties desk is loaded into the info mannequin.

Knowledge Modelling

Within the earlier part of this weblog submit, we ready the info primarily based on our necessities. The info is now loaded into the info mannequin. If we take a look at the necessities once more, we see some factors that we have now to maintain. Our necessities have two major components to analyse the info for At the moment’s Duties and All Duties. To analyse At the moment’s Duties, we require to determine if the DueDate falls into at the moment’s date. To have the ability to deal with date-related calculations, we require to create a Date desk. Learn extra in regards to 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 advanced. I take advantage of a simple model utilizing the CALENDARAUTO() operate.

  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()
        )
Creating Date Table in Power BI Using DAX
Creating Date Desk in Energy BI Utilizing DAX

Marking Date Desk as Date

Up to now, 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 must always try this. Observe 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 sure that the validation was profitable
  4. Click on OK
Marking Date Table as Date
Marking Date Desk as Date

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 in regards to the idea behind the relationships right here. We’ve got just a few choices with regards to create and handle relationships:

  • We are able to use the Handle Relationships button out there on the Report view, Knowledge view and Mannequin view (proven within the following picture)
  • Or we are able to drag-and-drop key columns from a desk to a different
Manage Relationships in Power BI Desktop
Handle Relationships in Energy BI Desktop

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

  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
Creating Relationships from the Model View with Drag-and-drop
Creating Relationships from the Mannequin View with Drag-and-drop

Learn extra about Lively vs. Inactive relationships right here.

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

Creating Measures

Probably the most widespread and undoubtedly crucial duties whereas creating a Energy BI knowledge mannequin is creating measures. With measures, we are able to do calculations corresponding to summations, averages, counts, and many others. There are two sorts of measures:

  • Implicit measures: Implicit measures or automated measures proven with a Sigma icon (Implicit Measures In Power BI ) within the Fields pane in Energy BI Desktop. These are the measures which can be robotically 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 (Explicit measures in Power BI).

Learn extra about measures right here.

It’s best apply at all times to create express measures. After I point out making a measure, I seek advice from express measures. We should at all times create the measures to help 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 essential duties
    • Variety of open duties
    • Variety of accomplished duties
  • Measures to calculate for at the moment’s duties:
    • Variety of duties
    • Variety of essential duties

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

  1. From the Report view or the Knowledge 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)
Creating a Measure in Power BI Desktop
Making a Measure in Energy BI Desktop

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

Measures for All Duties

Necessary 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 the moment’s Duties

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

  • Duties beginning at the moment
  • Duties due at the moment
  • Duties with out StartDate and DueDate

So we are able to 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 beneath:

Duties Beginning At the moment = 
    CALCULATE([Open Tasks]
        , 'Date'[IsToday]
        )
Duties Due At the moment = 
    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 the moment's Duties = [Tasks Starting Today] + [Tasks Due Today] + [Tasks with No Start or Due Date]

The final measure to create is At the moment’s Necessary Duties. The next DAX expression caters that:

At the moment's Necessary Duties = 
    CALCULATE([Today's Tasks]
      , Duties[Importance] = "Excessive"
    )

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

Knowledge Visualisation

Up to now, we ready the info and constructed our knowledge mannequin. It’s time now to convey our knowledge to life and constructed some significant knowledge visualisation. Once more, the final rule of thumb is to take a look at our necessities first then begin visualising the info. In your comfort, I copy the necessities right here to keep away from shifting up and down on this weblog submit.

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

One of many largest challenges in knowledge visualisation, whatever the visualisation instrument we use, is actual property. It’s fairly difficult to make use of the out there area on the report canvas to signify the data effectively. It isn’t value constructing a flashy and vibrant report that doesn’t inform a narrative in regards to the knowledge and doesn’t reply the enterprise questions mirrored within the necessities. Knowledge visualisation is a serious 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 submit. So, I put collectively a easy knowledge visualisation that meets all the necessities.

All To Do Tasks
All To Do Duties
Today's Tasks
At the moment’s Duties

Within the subsequent few sections, I rapidly clarify some easy methods used within the above visualisation, leaving the remainder so that you can examine.

Exhibiting a URL hyperlink as an alternative of displaying a textual full hyperlink could be very simple. Observe these steps to get it completed:

  1. Choose the Internet URL column from the Duties desk
  2. Choose the Internet URL possibility from the Knowledge 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 possibility
Showing URL Link in a Table Visual in Power BI
Exhibiting URL Hyperlink in a Desk Visible

Filtering the Knowledge to Present At the moment’s Duties Solely

As you may see within the At the moment’s Duties report web page, we have now a Desk visible containing the required columns from the Duties desk displaying all duties. We require to filter the info proven on the desk solely to indicate at the moment’s duties. You could assume that we are able to 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 by way of the connection between the Date column from the Date desk and the StartDate column from the Duties desk. Due to this fact, the values on the Desk visible are filtered solely when the beginning date is at the moment. However this isn’t what we require. The necessities clearly say, “At the moment’s duties are all duties that their StartDate or DueDate is at the moment or the Duties with none StartDate and DueDate”. So we can not merely filter the values of the Desk visible by the IsToday column from the Date desk. The answer is easy. We already applied the required logic within the At the moment's Duties measure. We are able to put this measure on the Desk visible, which ends up in displaying the right values.

Today's Tasks Measure Added to the Table Visual
At the moment’s Duties Measure Added to the Desk Visible

Whereas it could look to be a superb resolution, it isn’t splendid. In actuality, we’re not including any insights by including the At the moment's Duties measure to the Desk visible, because it exhibits 1 in nearly all rows. Including a measure to a visible to solely clear up our downside with out including any worth is inappropriate. A greater resolution is to filter the Desk visible by the At the moment's Duties measure. Observe these steps to see how:

  1. On the At the moment’s Duties web page, click on the Desk visible
  2. Proper click on the At the moment's Duties measure from the Fields pane
  3. Hover over Add to filters and click on Visible-level filters
Using a Measure in Visual-level Filters
Utilizing a Measure in Visible-level Filters
  1. On the Filters pane, choose the isn’t clean possibility from the Present gadgets when the worth dropdown checklist
  2. Click on Apply filter
Filtering Visual Values Using a Measure in Visual-level Filters
Filtering Visible Values Utilizing a Measure in Visible-level Filters

It’s completed now.

Downloading the Pattern File

You may obtain the PBIT model of the report from right here.

As at all times, please let me know you probably have any feedback or suggestions by way of the feedback part beneath.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments