A while ago I met the Head of Human Resources for a Financial Services Company. And you can very well guess that the nature of the meeting sure had to do with solving a peculiar spreadsheet problem.
Here is (a part of the problem) what her team was grappling with
This sounds simple right ?
But this is a terrible way of storing data. If you are unsure WHY?? Let me throw one questions at you
Question – How many unique training programs did each employee take ?
I think I got you now, but in case you aren’t convinced please note that the real time data will not be as tiny as the snapshot. You’ll have hundreds of employees and dozens of training.
That makes it hard to do it manually
You can DOWNLOAD THE EXCEL FILE HERE
I have found many companies capturing similar data incorrect way. Please post your solution (powerquery or otherwise) in the comments. I would love to see multiple ways of solving this