I’ve been using To-Do for my tasks for quite some time now and recently posted some statistics that I got from connecting Excel to my Exchange Mailbox. I realized this was something I wanted to keep track of over time and decided to build myself a Power BI Dashboard that updates daily!

First of all, open Power BI Desktop and connect to Exchange Online

Fill in your email address and click OK.

Sign in using your Microsoft Account. Click Sign in.

When you click Sign in, you’ll get the regular log in prompt for Office 365. Once authenticated you will get this message.

When you click OK, you get to choose what to gather from your mailbox. As you can see, you could get stuff from emails or calendar as well! (I once looked into how much time I spent on travels during working hours based on all my calendar appointments).
Anyhow select Tasks och click Edit on this page.

I have quite a few lists, but my list for “Next steps” is the one I want to keep updating most frequent and so I simply filter all tasks in that list like so:

There’s a column named Attributes. By clicking to two arrows you get a list of new columns hiding behind this table. Here we’ll find the DateTimeCreated column. Select it and Click OK.

That’s really all I need for this dashboard, so I’ll right click on the column and choose remove other columns.

I also changed to type of this column from DateTime to just Date.

With my single column left, choose Group By and just go with the default counting proposal. This will group together all unique values of the dates and count how many tasks where created in that day!

If you want to take the shortcut, just copy and paste this into the advanced editor of Power BI Desktop and change the variables to your own settings.

let
Source = Exchange.Contents(""),
Tasks1 = Source{[Name="Tasks"]}[Data],
#"Filtered Rows" = Table.SelectRows(Tasks1, each ([Folder Path] = "")),
#"Expanded Attributes" = Table.ExpandRecordColumn(#"Filtered Rows", "Attributes", {"DateTimeCreated"}, {"DateTimeCreated"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Attributes",{{"DateTimeCreated", type date}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"DateTimeCreated"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"DateTimeCreated"}, {{"Count", each Table.RowCount(_), type number}})
in
#"Grouped Rows"

Now let’s create a simple card from the visuals. Drag Count into it and give it a good title.

While selecting the card, drag the DateTimeCreated field to Visual level filter.
Filter type: Relative date filtering

Change the calculation from Sum to Average.

Go ahead and Publish your new report!
Sure you might want to create some more visuals first, but I’ll save it for another blogpost.

In the report in powerbi.com, click the Pin icon and pin the visual to a dashboard. I’ve created a new one here but preferably you’ll have a Dashboard with important stuff from different reports gathered.

If you have a Power BI Pro license, you can schedule a refresh of the report! Go to Dataset in powerbi.com

Here you will have a dataset with the same name as you report. Click on the three dots and select Settings from the menu.

First of all you need to edit your credentials to allow the dataset to connect to the data source.

Choose OAuth2 as Authentication Method and log in using your Office 365 account.

When that’s done, you can expand Scheduled refresh and set “Keep you data up to date” to On. Choose daily and select your time zone in the drop down menu.

And that’s it! My next step from here was opening the Power BI App on my phone and then syncronizing my new Dashboard to my Apple Watch.

Now what I need to do is establish the habbit of looking at my dashboard in the apple watch daily to make sure I have a good average amount of tasks created as next steps. My current value is 2,6 and I’m setting my first goal to 5 steps per day to see how that works out. If you have any feedback or input that might help me in this fight for a more productive life, please reach out! I’m always keen to learn and improve.

Power BI is part of the Power Platform alongside with Flow and PowerApps. If you’re interested in knowing more, attend the Pre-day session at TechDays with me and my Collegue Magnus Göransson!