Transforming data in Power BI

- [Instructor] Power BI offers two core functions for the data analyst: Transforming the data, as well as presenting the data. We want to analyze the sales of our products to note our top 10 products. We will eventually visualize this data for an executive meeting or a future dashboard. The opening screen of Power BI desktop is a blank page ready for visualization. This happens after you've connected to your data. If you notice in my Field pane on the far right, I've connected to the tables I need to analyze the top products. It's the Order Details and the Products. We'll do the transformations on Product and Order tables in Power Query. I'll get to show you the Group By function, so I can total the orders. As well as the Query function, where I merge the orders and the products together using the Merge Queries option. Because I've connected to my data, I can just go to the Transform option and begin my data cleanup. I'll start with Products. The only information I need for the top product analysis is the product ID and the product name. So I'll click Product ID. Hold my Control key and choose Product Name. I'll right-click and Remove Other Columns. This just leaves me with the two that I need. Because this information might present better with the product name being all uppercase, I'll go ahead and right-click, and transform it to be uppercase. I'm now ready to move on to Order Details. Order Details gives me the order ID, the product ID, the unit price of that product, how much was ordered, and the discount. One of the very first things I need to create is the function that gives me the total amount after the applied discount. Okay, I'll choose Add Column. I'll do a Custom Column. I'll do Total Order Amount. In this statement, I'm going to create the subtotal, calculate the discount, and then deduct them from each other. Again, mathematically, you could do this multiple ways. Okay. I'll click OK. And now I have my quantity times my unit price minus the discount amount. I want this to be a fixed decimal number. Fantastic, I now have my Total Order Amount. Now I'll use the Merge function to create the query that merges the Products table to the Order Details table. I'll start by clicking on Products. I'll go to my Home tab. And I have the option for Merge Queries. We have two options here: Merge Queries or Merge Queries as New. Merge queries, if I select it, will allow me to merge data directly into Products. Merge Queries as New will give me a third object to work with. For the example in what I'm creating today for analysis, I just need to do a Merge Query. I can merge that data directly into Products. Now I have the Merge screen and I have Products. And I want to merge it with Order Details. And the common field between the two is the Product ID. So I want to make sure that I've highlighted those. The join types, just like any other data set, are here in the Merge. If you look at the bottom of the screen, it says Join Kind. And if you notice, there are 75 records that match 77 rows from the first table. That means I actually have two products with no records. Meaning, they haven't been ordered. And that's okay. We're looking at the top products, so obviously, all of them wouldn't have been ordered. I'll go ahead and hit that drop down. I have Left Outer, which would show me what it's showing now, all products, and if it hasn't order. Right Outer, which would show me all order details regardless of the match to the product. A Full Outer, meaning, if I have products and order details that don't have records matching, it would show all rows from both. An Inner join, which is what I need here, show me just products with orders. You also have Left Anti and Right Anti. This would show you just the null values. So if I were to choose Left Anti, it would only list the two products that didn't have order details. For my top analysis, I need Inner. I'll go ahead and click OK. And now I can expand my table. I'll hit my Expand here. I don't need to use the original column name as a prefix, but that's a preference. I don't need all of the columns. I really just need the Total Order Amount. I can go ahead and click OK. And now I see the Product Name and the Total Order Amount. Now I'm ready to group them up. This will allow me to use the Group By function, and total by each product. Okay, I'll go to my Transform tab. I'll go to Group By. Okay, I want to group by the product name. And I want to get a total... by summing up... the actual total order amount. This will take each individual line item and total it up by product. Giving me the total orders. I'll go ahead and click OK. Now I see each product name and how much was ordered. Okay. When I go back into my visualization, I really only need to see Products. So I'll go ahead and tell Order Details not to load. I'm not using it at any visualizations, so it's okay for me to continue here. All right, I'll go ahead and go to Home. And I'm ready to apply this data set... to my visualization page. So now on my Fields list, I see my Products. I'll visualize this in a table, so I'll choose Table. And then I'll drag my Product Name... and my Total to the Values. Okay, I'll go ahead and size this out so I can see it. Now, right now, this represents every single product. And we're trying to get to the top 10. I'll go to the Product Name on the filters. I'll tell it to do an advanced Top N filter. Where top is 10. I'm going to base that on the total, so I'll drag that Total to the By value. And then I'll apply that filter. After applying that filter, I see the top 10 products. Let's go ahead and sort it. Just by clicking that Total header. These techniques and joins show you exactly how powerful Power BI and data can be when you establish cleaning routines for basic presentations of data.