Oz du Soleil shows an example of what you can do in Get & Transform in Excel 2016.
- [Instructor] What's the big deal with Get and Transform and why should you get to know it? I've been training people for awhile and people have emailed me that Get and Transform has saved them a full day a week. One person said that she saved her employer $30,000 a year because of what Get and Transform can do. You see, analysis is one thing, but the data prep the data cleansing that can take hours, days, weeks to do. And Get and Transform is specifically designed to bring that time down, bring down the grief, bring down the pain.
In this video, I'm going to take you on tour of Get and Transform. There is no exercise file. So, sit back and follow along. Here, I'm in Sales Ops. And I have this report and it's easy to see that Amanda, Northwest Territory, February had sales of $16,494. But what if I want to see totals? Notice Jean-Felipe. He's got accounts South and Southeast. I would like to see one Jean-Felipe entry.
I don't want those IDs. Get rid of them. And I would like a month column, not those headers, and clos in those gaps in the region column. I'm going to tell you back before Get and Transform, I did have to do something like this monthly and it was painful. I hated it. But you'll see in Get and Transform right now, how easy this is to clean up. With the cursor in the dataset.
We're at Data. From Table we're starting a query. Here is the data as it was in the workbook. Notice in the right pane called Query Settings, we have Applied Steps. The source, let's click on Source, and then changed the type. Notice how we go from Source and Changed Type. Get and Transform has guessed that these are whole numbers. So, now watch. I'm going to go to region and close in those gaps.
Highlight that column, right-click, Fill, Down. The gaps are closed and notice in the Applied Steps there's a field Down Step. We can delete that step. And let's redo it. Fill, Down. The next thing, we do not want those IDs. Highlight that column. Split column, By Delimiter. It defaults to Comma.
We want it at each occurrence of the delimiter, OK. Now, we can get rid of this column. Next, we wanted a month column not those monthly headers. So, let's highlight Region, hold down the Ctrl key and select Rep, cause I'm going to do an Unpivot. Transform, Unpivot Other Columns. Let's rename this Attribute as Month.
Let's rename Value as Sales. This is exactly what we wanted. So, let's go ahead and close and load this to the Workbook. Exactly what we wanted. Now, we can do some simple analysis. Let's insert a Pivot Table. New worksheet. Let's put rep in Rows, Month in Columns, Sales in our Values.
Let's remove Month and put Region. Now, we can see Jean-Felipe's accounts in those two different regions and his total. But now, pay attention to this. Let's go to Sheet1. Lord, we got more data. Unhide this. Lord, what are they doing to us? Add that in. The table absorbs it.
Do we have to go back into Get and Transform? No. We're going to highlight that Pivot Table, go to Data. We're going to hit Refresh twice. Refresh. That refreshed the query. Refresh again. Ho! Look at that. We got more people, we got more regions, everything updated. That's Get and Transform. But notice something else. Here's something, Rep comma ID.
That still is doing that. Let's highlight this and fix that. Edit. I just want to call this Rep. Close and load. Refresh. What's happened is the Pivot Table was looking for that Rep comma one. We just need to bring Rep back. See? And this is an example of if somebody had to do this on a daily or weekly basis, and she's able to do this going forward just with the hit of a Refresh button, that's how you reduce anxiety, make your life better, and save an employer $30,000 a year.
And that is the power of Get and 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