Ad hoc reporting

- [Instructor] As you become known around your office for your data skills, you'll be faced with what's known as ad hoc reporting. These are the ones time requests from your leadership or manager, or sometimes even colleagues, reports like top customers or numbers of orders not invoiced or even forecasting are examples of ongoing reports that drive business decisions but ad hoc, I mean requests that aren't a standard part of your workload and they're requested informally sometimes by colleagues. You may have also heard these referred to as one off requests. Ad hoc requests are typically something you're going to perform one time and it's not going to require the same time and effort as an organizational wide report. Although it doesn't have the same time and effort, it does use the same exact tools and concepts so you still have to validate and verify all the way through and you still have to confirm your business roles. Ad hoc requests can help build your skills and help grow your data capabilities. Let's say, for example, you work inside the sales department and they want to know how many Purchase Orders and the total amounts for those Purchase Orders for each customer are in the system. You have all the data you need to perform this ad hoc request. Simple data tasks can be done a number of ways. And I've found though we're creatures of habit so we might be slow to adopting newer and better tools like Power Query. So let's open up our ad hoc file from her chapter folder and let's see if we can accomplish the ask of how many Purchase Orders, what are the total amounts for each customer. We've already confirmed with the person who's requested this information that all we need is just the list of the customers, account of their PO numbers and the total dollar amounts based on the Line Total that's in our file. This might be a good time to rewind a few seconds and note out exactly what we're trying to accomplish. Remember it a really good practice so that you and the stakeholder both know what you're expecting when the analysis is finished. The very first thing we want to do is connect this data to Power Query. And if you need a refresher on how to do that, check the foundations course in the library. I'll go to data and I'm going to pull this from Table/Range. Here, I'll go ahead and click Okay here. I really only need the three values, Customer Name, Line Total, and Purchase Order. These are the three data points that make up my ad hoc request. I'll go ahead and do Choose Columns. I'll unselect, Select All. I'll do Customer Name, Customer Purchase Order and Line Total. I'll go ahead and click Okay. So now I have the three data points that I need for my actual ad hoc request. So I'm going to go ahead and sort by Customer Name. So I see my Customer Name and also want to go ahead and sort by my Purchase Order Number. And it might be a good idea to go ahead and move that over. So I'll move that field. And again, you say it's keeping up with what I've done in my applied steps. All right so I have Customer Name, I have their Purchase Orders and then I have their Line Totals. Now what's important to note is that I have the customer. And then here you see, this is the Purchase Order and it's repeated because there are multiple lines on this Purchase Order. So the very first thing I'm going to do is work with a Group By. I want to Group By my customer and then Group By the Purchase Order and then I want to total up those Line Totals. So I'll go over to my Transform and I'll choose Group By. Okay, I'll select advanced. I'll do a customer grouping. I'll then go add a Purchase Order grouping. I'll go ahead and add Total Amount, where I actually sum the the Line Total. I'll click Okay. Great. Now let me go ahead and sort by my Customer Name again. It's not necessary for me to do that, but I like to see my information lined up. So here that same Purchase Order request was 10168 and here it is now totaled. This particular customer has 11 different Purchase Orders. And that's part of my requirement. I need to actually count the Purchase Orders. And then I also want to total up the amount of all of their Purchase Orders. So I actually go to Group By one more time. I'll Group By, I'll do Advanced. Again, I'm going to Group By Customer Name and this time I'm going to type count of PO's. I'm just going to count the rows and then I'm going to have total amount and again, I'm going to sum the total amount. All right, I'll go ahead and click okay. So now I have each customer, a count of their PO's and the total amount of all of the PO's. So this 54,813 represents the total amount of 11 PO's for this customer. Okay, let's go ahead and go to Home. I'll do Close and Load. So I have 660 rows of information loaded here, based on my two Group By statements. Now, once I've taken this over to my requester, and they've taken a look at it, they've realized it's a lot of information here. So what they really want to do is see all of the customers who have totals that are over 55,000. So let's go back to our data. I'll hit the drop down by my total amount. I'll do a number filter of greater than or equal to 55,000. Now go ahead and click okay. Now I have 19 rows of data. I'll go ahead and close and load. We'll go ahead and name this PO's by customer over 55K. Make it real clear so we all know what we're working with. Ad hoc requests when you have data skills is unavoidable. It's also important to remember your best practices because you never know when that ad hoc request is going to become the next big organizational report.