Oz du Soleil takes you through a real-world scenario using Get & Transform to convert a survey report into a flat file. Oz shows you unpivot, fill down, and other features to convert the data into something useful.
- [Male Narrator] Alright now, here is where it gets good. We get to tie everything together that we've seen in the course, and I'm about to issue you a challenge. I hope you're ready. Here we've got data from a magician's conference where they were learning to levitate stuff. Now, we would like to turn this survey data into data that we can use to do other analysis with, but we need raw rows and columns. So here is the challenge.
First of all, we don't need the workshop Magic Mastery: Levitation data. We know what workshop this was. Column C is completely empty. We don't need that. Then we have empty rows. Like row seven is empty, row nine is empty, row 17. We don't need the averages. If we want those later we can calculate 'em easily. And then the big thing is the "would you recommend this workshop?" through "instructor".
We don't want those as column headers; we would like to have a questions column and have it going vertically, all one big stack. So if you ready to take this challenge, pause the video, work it out, and I'll meet you back here and show you my solution. Alright, you're back, and I hope you were successful. If not, that's fine. We all have to learn, and I'm glad to show you how this works.
First of all, there's too much going on to try to put this into a table. I'm going to open a brand-new workbook and import this data in. Data, New Query, From File, From Workbook. Now we get to the workbook. Desktop, Survey Results, my navigation pane, do we want that sheet, edit ... We can start by, let's get rid of column three.
That was completely empty. We don't need the workshop column. Right-click, remove that. Now this is where we have to be really creative. The rows where there are nulls, we got to get ride of those, but we also don't want to remove nulls where there are values that were just empty. Here is how we're going to approach this. If we do a fill down, average will wind up in the places where we want to delete anyway.
So notice the average at row seven. We filled down is going to go over the nulls in eight and nine and stop at Wednesday. Let's do it. Right-click, Fill, Down. Now we can get rid of seven and eight, and we can get rid of nine because those are excessive headers. We don't need those anymore. Filter out anything that says average. Okay.
Now we've got one big block of data. Now we have to unpivot our questions. With Day already highlighted, we can go to Transform. Unpivot Other Columns because I don't want to unpivot Day; I want to unpivot the rest of those columns. Beautiful. Now let's close and load this to the workbook. Check it out.
Here's our data, exactly what we wanted. Let's insert a quick pivot table. Insert, Pivot Table, okay. Let's put Day in columns, Value in values, Attribute here, and let's do one other thing. Let's turn this into average. Value Field Settings, let put this as average. Okay.
Let's change the decimal points. Now check that out. Now we can see that consistently Tuesday, Wednesday, and Saturday, people were not happy with this venue at all. On Wednesday, it got an average of 2.8. This is exactly the result we were after. I hope you got that, and if you didn't, that's okay. It takes a while, and just stick with it.
You'll be alright.
- 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