From the course: Learning Data Analytics: 1 Foundations

Solution: Count and amounts of products

From the course: Learning Data Analytics: 1 Foundations

Solution: Count and amounts of products

“

(joyful music) - I do hope you enjoyed that challenge. Let me show you how I would attack it. Remember, there are different ways to get to the end result. So, if I do mine a little bit differently, don't worry. We'll know if we got the same totals or not. I'll begin by going to my data tab. I'll do table a range. I'll click okay. This will type into power query. One of the first objectives is to actually combine the first and last that've been customer key. And again, there were not a lot of instructions on how to make that look. So if you did something a little bit different it's okay. I want to start by grabbing my first name, holding my control key, and grabbing the last name. I'll merge those first. Okay. I want to separate those with a space and I'll call them first last. Okay. I'll go ahead and move my customer key over. I'll grab my first last. Hold my control key and do my customer key. I would like a dash between these so I'll go ahead and right click and merge columns. I'm going to call this customer name key and I'm going to separate it with a custom. Little space dash space and click okay. Perfect. I made my first objective. Now the next thing it said was they wanted us to take that product key and convert it to text. So I'll go ahead and hit that data type drop down and make it text. All right. Now I'll expand my queries. It asked for a separate dataset. So I'll go ahead and name this one, original, and then I'll go ahead and duplicate this dataset and call this, totals and products. Okay. I'll go over to my transform tab and I'll do group by. So I need a total number of the products ordered and then also a total amount of the sales amount for each product. Okay. So I'll go ahead and do my product key. I want to go ahead and actually I'm going to sum, so total quantity and I'm going to sum up that order quantity. Now some of you may have just learned something. You may have actually counted the rows of the product key which that would actually count how many times that product key shows up in our record set. But if anybody ordered more than one, well then we would need to sum up the quantity. Okay, I'll go to my advanced, because I also want to get a total sales amount and here I'll do sum and I'm going to sum my sales amount. I'll click okay. Well my total sales looked like money so I'll go ahead and change that to currency. Great. I'll do close and load. And I've now completed my challenge.

Contents