Merge Electronically Sign Calculated with airSlate SignNow

Merge smart calculated

[Music] hi in this video I'll show you how to group and merge data to perform some basic calculations using power query now the use case of this is as follows we have this problem we're trying to find out the completion rate for employees under certain managers over a multiple quarter effort and this is because they're trying to train a workforce over a multiple core effort the complication is courses will be added this is not just one course this will be courses that add will be added along the quarters maybe quarter one quarter two quarter three there's gonna be different classes that are offered and of course we're gonna assume that employees will change managers there they'll move from manager one to manager - or there's going to be new employees under managers now we're going to assume the input are simple EMS workers educational management system records that we have we have our course we have the ID of our workers and we have their status or this drop or they complete it what not now we also have HR data it may be the EMS workers doesn't have HR data which aligned them to a manager so I have HR data and a simple HR data would just have the worker ID and their manager so with our output we want to know in this case what the course is for that manager their total employee count and the course complete count and the completion rate now we have to understand that the courses will be added and we want to do this kind of like as it rolls along for each quarter and of course employees will change managers or new employees will be added to managers this could be done with taking the EMS records that table and taking an HR record table doing pivot and manually inputting it into another table and then this is a complication to this is let's automate this right we want to do this where when we have our any updates to the EMS records or the extra data and get the output of course 1 and maybe of course - of course 3 all we need to do is press one button and it automates on this instead of us trying to pull it into another table doing some lookups and getting the calculation so let's see how we do this first off we have our courses here and our worker manager right so this is the courses here this was the EMS data here that working manager is our HR data let's turn these into tables I'll select that go to insert table yes my table does have headers click OK I'm gonna give it a name worker underscore status do the same thing for a worker I'm manager here go to insert table click OK give this table name - let's call it worker underscore Manager MRG M gr I'm gonna bring these...