Oz du Soleil takes you through a real-world scenario in Get & Transform. It is a strategic approach using features like: import data from an Excel file, unpivot, join, left outer join, splitting columns by delimiter, and add custom column.
- [Narrator] Here's a real world example looking at pie orders. We've got orders on the left. And on the right side is our pay grid. In sales C3 we have sweet potato, then __W, which means a whole sweet potato pie, and what is the price? Over in L3, it's $26. And we look back at D3 and see that five were ordered.
So going down this whole list of orders, what is the dollar amount purchased for each type of pie? So if you're up for this challenge, pause the video, give it a shot, and I'll meet you back here and we'll go through my solution. All right let's jump in and see how this works. Here is how I am thinking about this. We've got to do two different queries. The orders query, and then we've got to unpivot that payment schedule so that we have the prices all in one stack.
So let's go ahead and do that. Select the data over here. From table, my table does have headers, okay. With pie highlighted I'm just going to unpivot this data set. Right click, unpivot other columns. Now it's easy to see. Row 10, a slice of apple pie is $6.
That's all we want to do here. Close and load this as a connection only. And let's rename this query, prices. Okay. Let's create this query. Data, from table, table does have headers, okay. We don't need the phone, online, web site, no.
So let's select this column, filter out, no. Now I need to separate, for example, sweet potato and the W for whole pie, so we're going to split the column by the delimiter. It's guessing that I want to it at a underscore. It's actually a double underscore. Okay. Let's rename this as flavor, let's call this size, notice in the previous query we don't have W, S, and M, we have whole, slice and mini.
So let's go back to the that query. We've got to highlight this column, and then we're going to split by number of characters. Split by one, as far left as possible, okay. Now we have our S, Ws, and Ms. Right click, remove this column. Go to table two, rename this as orders, close and load this as a connection only.
Now, let's join the queries. We need to take the orders and retrieve the prices. So we're going to make the orders our left side, prices are right side, and do a left outer join. Data, new query, combine queries, merge, we'll put in orders up top, prices, this defaults to left outer join. But now we're using two criteria.
We have to match pie and flavor, hold on the control key, attribute one, and size. Okay. Let's expand, we want to unclick this, and we don't want pie. We already have that. Okay. All right, and we can verify, whole, whole, that matches up.
Okay. Let's actually remove this column. Now let's add a custom column so now we can get the prices. Add column. Custom column. Call it total, equals, quantity times value. Okay. We have exactly what we want. Now we can do anything from here. Let's close and load this to the workbook.
And now wrap this up with a pivot table. Insert pivot table. New worksheet. Flavor. And our total. And that is exactly what we wanted. We can convert this to dollar amounts. And we've got it. Cherry bourbon pie generated $863, and if we want to go further and look at the size, check that out, now we can see.
Cherry bourbon pie, $70 worth of slices. Here's the result we wanted, and if you didn't get that, that's fine. Hopefully you can identify where you got stuck and go back and revisit those parts of the course. Then, I invite you to come back and try this again, and hopefully you will get the answer that time. So thanks for joining me on this course, and this introduction to Get & Transform.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins