Task Tracking with Power Query

Did you know Power Query can be used as a task tracking tool? This might sound quite unusual but the method described here has been used for solving a real business case. The example I will use is rather simplified but still close to reality, and will demonstrate how to build task tracking with Power Query.

Laying out the Scenario

Vicky is a manager of a small team that is dealing with customer questions on various topics. One of her duties is to distribute various questions among her subordinates. After that, each of them should take some actions and report what is the status of each task.

The problem is – how can each employee can see what tasks are assigned to him/her and fill in the respective information for each task? At the same time, Vicky should at any moment be able to assign a new task and review the statuses of old ones. This is the table Vicky needs:
Task Tracking with Power Query

Unfortunately, she has no other tool at hand except Excel. Luckily, she can set up task tracking with Power Query right in Excel, which could work perfectly in this case.

Setting up Task Tracking with Power Query

So let's start building the solution.

1. Load the left table (in this example, called Filled by Manager) into Power Query.
Manager's table to assign tasks

2. Next, create one query for each employee by filtering the Employee column.
Create individual employee queries

3. Load each Employee table into a separate Excel sheet. (Of course they can be on different files linked to the source table).
Sample employee table showing tasks assigned

4. Then, create a table for each employee to fill in the actions and statuses.
Employee's task tracking worksheet

You can see in the above picture what each employee will have in his/her worksheet - a green table on the left with the tasks assigned to them, and a yellow table on the right where he/she has to fill in the respective information.

Creating the Filled By Employees Table

5. Load all the Employee tables into Power Query.
Load all the employee tables into Power Query

6. Append them in a new query (in this example, called Statuses).
Append all the employee tables into new Statuses query

You are probably guessing what the next step is – load the Statuses query into Excel right next to the Filled By Manager table

However, the result is not what we would expect.
The Filled by Manager table is not matching the newly loaded Fill by Employees table

Note that on first row of the Manager’s table is a task assigned to Ivan on 27.01.2019, but row 1 of the Employee’s table shows the task assigned to Maria on 09.02.2019.

In order to fix this mess, we need one additional query.

Building the Task Code Query

7. Once again, load the Manager’s table into Power Query and remove all columns except for Task Code.
Task Code Column

Task Code is a unique identifier of each task. In this example, it is simply composed of the employee's name and the number of occurrences of this name in the register up to the respective row. In Excel language, the formula is:
Use COUNTIIF to create unique task identifiers

The trick is that we fix the first row of column F (containing the employees' names) but the end row is not fixed.

8. Merge the Register Employees and Statuses queries together.
Merge the Register Employees and Statuses tables

9. Finally, expand the table and voila - it is in the required order. The only thing left is to load it back into the Manager’s table.
Final table for task tracking with Power Query

Now, any time she needs to, Vicky can refresh the Filled by Employees table and see the updated statuses of each task.

Likewise, each one of her subordinates can simply refresh the Manager’s table (the green one that is on left of his/her tab) to see any new tasks that have been assigned.

You could also automate the refresh operation VBA. For more details, refer to Chapter 16 of Ken's M is for (Data) Monkey book.

Final Words

This article presents nothing new and unusual as a Power Query technique. What is new and unusual is the way Power Query has been used for solving a typical business problem. This is just additional proof of how powerful and useful this tool is.

You can find the file with example here: Task tracking with PQ

Check the application version in Modern Office

In the good old days, it was easy to check the application version in Office with VBA.  You just used a little test of Val(Application.Version) to return the number.  12 was Office 2007, 14 was Office 2010, 15 was Office 2013, and 16 was Office 2016.  But then Office 365 came out, and 2019, and things fell apart.

Conducting a check of the application version in Modern Office is not as straight forward.  From Office 2016 onwards, Microsoft has not revved the Application.Version number - they all show as 16.0 - giving you no way to differentiate between versions.  (Bastien discusses this in a blog post a few months ago.) But worse, while he focuses on 2016 vs 2019, there is also no way to test between these and Office 365 subscription versions.  As there are now things that work differently for Office 365 than the perpetual licenses, this is another potential problem for developers.

This past week I ran into a scenario where I needed to do exactly this.  I needed to find a way to programatically enumerate whether a user is running Office 2016, Office 2019 or Office 365, as I had to do something different in each case.

So how can we check the application version in modern Office?

After doing a little digging, I finally found a registry key that seems to appear in Office 2019 and Office 365, but does not exist in Office 2016.  So that was good news. And even better, that key holds values like "O365ProPlusRetail" vs "Office2019ProfessionalPlus".  While I haven't tested with other SKUs, this would seem to indicate a pattern I hope we can rely on.

Given that, I've pulled together this function.  It's purpose is fairly simple: Test the application and see if it is a perpetual license or a subscription install, and return the version number.  So anyone with Office 365 installed should receive 365 as a return, otherwise you'll get a four digit number representing the version you have installed.

Function to check the application version in Modern Office

Function AppVersion() As Long
'Test the Office application version
'Written by Ken Puls (www.excelguru.ca)

Dim registryObject As Object
Dim rootDirectory As String
Dim keyPath As String
Dim arrEntryNames As Variant
Dim arrValueTypes As Variant
Dim x As Long

Select Case Val(Application.Version)

Case Is = 16
'Check for existence of Licensing key
keyPath = "Software\Microsoft\Office\" & CStr(Application.Version) & "\Common\Licensing\LicensingNext"
rootDirectory = "."
Set registryObject = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & rootDirectory & "\root\default:StdRegProv")
registryObject.EnumValues &H80000001, keyPath, arrEntryNames, arrValueTypes

On Error GoTo ErrorExit
For x = 0 To UBound(arrEntryNames)
If InStr(arrEntryNames(x), "365") > 0 Then
AppVersion = 365
Exit Function
End If
If InStr(arrEntryNames(x), "2019") > 0 Then
AppVersion = 2019
Exit Function
End If
Next x

Case Is = 15
AppVersion = 2013
Case Is = 14
AppVersion = 2010
Case Is = 12
AppVersion = 2007
Case Else
'Too old to bother with
AppVersion = 0
End Select

Exit Function

ErrorExit:
'Version 16, but no licensing key. Must be Office 2016
AppVersion = 2016

End Function

If you'd prefer to just download a workbook with the code in it, here you go.

Care to help me test it?

I'd love it if people could give this a try and see if it returns correctly based on the versions of Excel you're running, particularly if you have a flavor of Office 365 or Excel 2019.

Let me know how it goes!

EDIT:  I have made a small change to the code and sample file in case "O365" is not at the beginning of the registry key.  This should pick it up no matter where in the key the 365 term shows up.  I am starting to wonder if this key is only present for Insiders.  So if you do test, please let us know what channel you are on in addition to whether or not it works!

Power Pivot eBook Coming Soon

It's been a long time coming, but we are putting the finishing touches on the third installment of our free 'DIY BI' series. Consequently, we are excited to announce that the Power Pivot eBook will be officially released on Tuesday, July 3, 2018!

Power Pivot eBook

This brand new book will feature five of Ken's top tips, tricks, and techniques for Power Pivot, including:

  • Hiding fields from a user
  • Hiding zeros in a measure
  • Using DAX variables
  • Retrieving a value from an Excel slicer
  • Comparing data using one field on multiple slicers

Power Pivot eBook

 

About the 'DIY BI' Series

This free eBook series is available to anyone who signs up for the monthly(ish) Excelguru email newsletter. The series includes four books, one edition each for Excel, Power Query, Power Pivot, and Power BI. Each book contains five of our favourite tips, tricks, and techniques which Ken developed over years of research and real-world experience.

DIYBI eBook Series

We first launched this series in the spring of 2017 with the Excel Edition, and the Power Query edition followed later that summer. You can read some more about why Ken decided to create this series in his initial blog post about it.

The Excelguru Newsletter

The monthly Excelguru email newsletter features the latest updates for Excel and Power BI, as well as upcoming training sessions and events, new products, and other information that might be of interest to the Excel and Power BI community.

Don't Miss Out, Get Your Free Copy of the Series

If you're not already a newsletter subscriber, you can sign up here. We will send you the Excel Edition right away, and the Power Query Edition a few days later. All of our current and new subscribers will receive the Power Pivot edition once it is released on July 3, 2018. Be sure to keep an eye on your inbox for the new book.

We will be continuing to work on the fourth and final book, the Power BI Edition, over the coming months so stay tuned for details!

Free ‘DIY BI’ e-Books

Today I wanted to just make a quick announcement that we are currently working on a series of free 'DIY BI' e-Books.

Free 'DIY BI' e-Books? Tell me more!

Over the past few years of working with Excel an Power BI, I've obviously picked up a few different methods, tips and tricks for working with the software.  And looking at how successful our free e-Book "Magic Tricks for Data Wizards" has been through the Power Query Training site, I thought it would be nice to so something similar for Excelguru readers.

One of the cool things about the Excelguru audience at this site is the diversity.  A lot of people originally came here for Excel, but we've been exploring Power Query, Power Pivot and Power BI for the past few years as well.  The one thing that ties us all together is that we are building "Do it Yourself Business Intelligence" or "DIY BI".

My original plan was to release one e-Book with 20 different tips, tricks and techniques; 5 each for Excel, Power Query, Power Pivot and Power BI.  After getting started, however, I realized that it was going to take me a bit longer to get that all done than I wanted.  But since I want to get information out to our readers, I've decided to break this down into four separate e-Books which will be collected under the umbrella of "DIY BI Tips, Tricks and Techniques".  Each e-Book will focus on one specific area of the DIY BI story.

What will the free 'DIY BI' e-Books include?

Well… tips, tricks and techniques, of course.  Smile  Okay, seriously, each is fully illustrated and written to give you some great examples and ideas that I hope will help you in your DIY BI journey.

Here is what is covered in DIY BI Tips, Tricks and Techniques for Excel:

  • The easiest formula to return the end of the month
  • Show a message when cells are hidden
  • Quick alignment of objects
  • Easy to read variances
  • Show a message if your Pivot data is stale

Sample image from DIY BI Tips, Tricks & Techniques for Excel

What areas will the free 'DIY BI' e-Books cover (and when will they be released)?

Those e-Books will be released in the following order:

  • DIY BI Tips, Tricks and Techniques for Excel
  • DIY BI Tips, Tricks and Techniques for Power Query
  • DIY BI Tips, Tricks and Techniques for Power Pivot
  • DIY BI Tips, Tricks and Techniques for Power BI

The first is already written, we just need to lay it out and make it look a bit more awesome.  Our target is to get it released by the end of next week.

With regards to the rest, I'll go as fast as I can on them, but as you can imagine, doing things right does take time.  I would expect that each will take 2-3 weeks to build out properly, but if I can get them out faster I most certainly will.

Am I going to need Excel 2016 to get value from the free 'DIY BI' e-Books?

No.  While I highly advocate being on a subscription version of Excel 2016, you'll find content in each of the first three e-Books which can be used in prior versions of Excel.

How do I receive the free 'DIY BI' e-Books?

You sign up for the Excelguru newsletter.  It's just that easy.  As soon as each e-Book is finished, we'll be emailing it to everyone who is currently subscribed to our newsletter.

And in the mean time, you also get a monthly email from us which now includes news about the latest updates to both Excel and Power BI.

Longer term, once all four e-Books are written, any new subscribers will receive the first e-Book upon signup, and then the next in the series will arrive every couple of days until you have the full set.

So what are you waiting for?  Sign up right here and don't miss out on free DIY BI Tips, Tricks and Techniques for your work!

Subscribe to our mailing list

* indicates required





Naming Conflict Fun

Jeff Weir published a post at DDOE last week on global names freaking out when a local name is encountered.  It reminded me that I ran into something similar when I was testing text functions in Power Query a while back; a naming conflict when I created a table from Power Query.

Interestingly, I can replicate this without Power Query at all using just native table functionality.

Set up a Table

To begin with I created a very simple table:

SNAGHTML868f307

Then I gave the table a name.  In this case, for whatever reason, I chose “mid”:

SNAGHTML869e527

Enter Wonkiness (Naming Conflict)…

Okay, it’s a weird table name.  I get that.  But in my original example I was comparing Power Query’s Text.Range function with the MID function, which is why I named my table mid…  anyway…

Add a new column and type in =MID

SNAGHTML86ce5c5

You can see that we’ve plainly chosen the MID that refers to the function, not the table.  I even set the capitalization correctly to make sure I got the right one.  Now complete the formula:

=MID([@Product],3,1]

SNAGHTML86e317f

And press Enter:

SNAGHTML86f7672

Nice!  Apparently Excel is too smart for it’s own good and overrules the interpretation of built in functions with table names, resulting in a #REF! error.

Fixing the issue

The solution to fix this should be pretty obvious… rename the table.  When you do, you’ll see that it also updates the formula:

SNAGHTML8739b0e

Plainly Excel was very confused! So now we just need to fix the formula:

SNAGHTML87501e1

And we’re good.  🙂

End Thoughts

To cause this issue from Power Query, you simply need to give your query a name that conflicts with an Excel function (like MID).  When it's loaded to an Excel table, that table inherits the query name as the table name.

The naming conflict issue has probably existed since tables were implemented in Excel.  It’s not good, but at the same time, it’s taken me a long time to trip on this, as I don’t usually use a table name that conflicts with a built in function name… at least not one that I use.

Long story short:  Avoid naming your tables (or Power Queries) after Excel function names.  😉

Quick Tip – Flip Numbers From + To – (Or Vice Versa)

I was working with some budget stuff today and wanted to forecast that a range of accounts would be written off. I had a forecast that showed the projected transactions monthly, (all zeros as the projects have been discontinued,) and the projected year-end balance. So I basically wanted to take the projected year-end balances, flip them from positive to negative and stuff them in the July transaction column. Here's how:

  • Copy the range of numbers
  • Right click the destination and choose PasteSpecial
  • Select Values and Subtract:

  • Say OK and voila:

Now interestingly enough, if there are values in the cells, it overwrites them. If there are formulas in the cells, it subtracts them from the formulas. Kind of odd that it isn't consistent.

(And of course, if you subtract a negative number, it turns into a positive too.)

Excel Events Calendar!

Hi everyone,

As per my last post, I recently set up a free public Excel Help Forum on my website. I'm pleased to say that over the past week and a bit we've attracted 50 new users, and now have over 40 threads with over 200 total posts on the site. Not bad for 10 days!

I've spent a lot of time configuring different options to try and make this forum as consumable as possible for people. In addition to the basic forum functionality, some of the modifications include:

  • Serious anti-spam solutions to keep the board all about questions and answers, without having to wade through garbage
  • Facebook integration, for those of you who like to tell your friends what you're doing 😉
  • RSS feeds, for those who like to keep up to date on the topics, and drop in when you see one that interests you
  • Tapatalk integration, for those who want to keep up with the site on a mobile device

And those are just the big ones. There's been a lot of tweaks under the hood to make the experience as optimal as possible, and a few others are coming soon.

The latest thing I've worked on is adding what I hope to be THE Excel Events Calendar on the internet…

I'd like to invite everyone to use the Calendar on the forum as a public Excel Events Calendar. If you have, or know of, an Excel event in your area, please post it for the world to see. I'd like to make this the most comprehensive Excel training calendar on the internet.

There are only 2 rules I'd like to attach to this at this point:

  • Put the location of the event in the title, if applicable. The goal is that people will be able to find events that they can attend in their own backyard, worldwide.
  • Only the date(s) of event should be posted. (I don't want a post each week reminding people.) If there is a registration cutoff date, please post it in the event information.

I think if everyone observes the above, the calendar should remain relevant and helpful to everyone.

Events I would expect to see include training courses (live or online), new book release dates, conferences that deal with subject matter relevant to the users on this site. (Be it BI, SQL, Sharepoint and more, so long as it has an Excel flavour, it counts.)

If you are a trainer, teacher, publisher, or whatever, I invite you to participate. Register for the site, if you're not already, and post your event to the Excel Event Calendar. The more up-to-date and accurate we can make this, the more our community can rely on this being the source to come to for training courses. The more that happens, the more likelihood you'll get signups. The more signups you get, the more likely you are to want to put on more training events, and the more the users win. It's a self-fulfilling cycle that is in all of our best interests.

Oh, and one final word on this to everyone. I am not doing this to solicit commissions, affiliate links or funding in any way. There is already enough advert links on the site. This is about people helping people in our Excel world.

New Excel Help Forums at Excelguru.ca

Hello everyone,

At the last MVP summit I had discussions with a couple of people about something that has been burning away in the back of my mind for quite some time... creating my own Excel forums.

I've been absent from the online forums for the last few years, and I've greatly missed it. With the amount of teaching I've done over the past few years I've constantly been referring people to other forums and blogs, and I've received several comments from those people asking about a forum on my own site. So during this time I've been musing it over, wondering if there is enough differentiation, or enough reason to take on the task of building and maintaining another forum out on the interweb.

Despite missing a few of my good friends at the last summit, it was one of the most exhilarating ones I've been too, for a variety of reasons. Some of it was technology based, of course, but there was some based on conversations with people that I never expected to have. The compounding of all of it together made me realize that I have been putting this off for too long, and that I actually needed to do it, to bring back the piece of me that I've put off for my job over the past few years.

To this end, I've begun the process of building another forum on the web, one that focuses on Excel and Excel users. I am hoping to grow it with the style of friendly community that was fostered at VBAExpress.com, but have the focus strictly on Excel users from start to finish, no matter the platform. There are forums there for Excel Client focused areas, Programming (VBA, .NET, XML, etc), PowerPivot and data needs, the recently released Excel WebApp and even Sharepoint. As I said, I want this to be a one stop portal for anything Excel related.

In addition to the Excel focused stuff, I've also added a simple section on the other Office apps. The reason for this is simple; every Excel user encounters the other Office apps, and runs into issues. I think it's important that we have a place to ask/answer those too.

Oh, and naturally there is a chat forum, as no forum would be complete without it.

The forums are, of course, completely free to use. Simply sign up for an account, and post your questions. You'll be notified immediately by email when someone replies, so you can jump right back to the forum and post further information if required, or see the solution that an expert has helped you build. You can even upload your own files to the forum so that people can see EXACTLY what you're trying to do. After all, a picture saves 1000 words, right?

And if you're an expert, or even if you'd just like to help, then post some answers! We love help, and encourage you to take part. Speaking from my own experience, I can tell you that there is no better way to better your own skillset than by trying to help others. You won't always have perfect answers, but nor do we expect that. This is all about people helping people. The intentions are what is important.

You can find them at http://www.excelguru.ca/forums. (The registration button is in the top right of the screen.) Or, if you'd prefer, you can jump straight to the Registration link to create your account.

I look forward to seeing you in the forums!

Question on your modeling practices…

So I've been working on a massive model over the past year, and recently recorded a webcast on modeling practices. In the course I cover some of my philosophies for making sure that the model lends itself to attracting valid data, namely I colour all input cells green and tell my users that green means go. This is a practice that I've followed for many years, and it's served us very well at work.

When working on my model though, just coding the input cells isn't enough. I need to enforce different looks for different cell types, and for this I reached to styles to help me manage consistency across the workbook. My custom styles gallery looks like this:

All the DE_ styles are data entry cells. The Head_ styles are for headers, and the SYS_ styles are for formulas and text that I don't want the users to change.

For a long time this worked out really well for me, but then I had to implement a change… I had to link to data in an external file. I try not to do that, and I need to be able to see this data in my model, but I didn't have a style to do this. Excel 2010's gallery has some default styles shown here:

As an accountant, I just can't have a block of Linked Cells with double underlines all the way through. Double underlines are for totals, not accents. So I ended up building my own style for this too:

I think it's equally ugly, but it does draw your attention to the fact that they're pretty dangerous.

I'm just curious as to what conventions you use. Do you highlight links in any way? Do you draw attention to internal links (to other sheets) or only external? What other things do you try to draw attention to?

Working with Styles

If you've followed my twitter feed over the past few months you've seen that I've been working on a couple of pretty large financial models. These models give us the ability to change a large variety of inputs in order to predict our real estate development over the next 25 years (through the build-out of our remaining lands), as forecast the effect of the real estate sales on our operating divisions (golf course, marina and fitness centre.) To give you an idea of their scope, they consume over 170 pages of 11x17 paper when printed.

This project was actually pretty neat in that I was able to sit down with people, scope it out in full, and apply every best practice to it as I was building. I'm really proud of these, and even based my recent course for CGA off the techniques that I used in these works.

In the end, I built two parts of the finished model, and inherited a third piece that had been built by someone else.

One of techniques I used was to apply Styles to the model. (If you're not familiar with Styles, then have a quick read of this article on my site.) As it turns out, that was a very smart move. Earlier this week I got the call that my headers needed to be changed, and I was provided an RGB colour scheme. In the model components that I built it took less than five minutes to update the Styles to the new colour scheme and the whole model was updated.

In the other model, the one I inherited, well… I'm still working on updating all the pieces. This time, however, I'm actually updating it with styles so that I'm not caught again if there is another change.