Oz du Soleil shows how to import data from another Excel file using Get & Transform.
- [Instructor] Get & Transform uses queries as its source. Therefore, to use Get & Transform, we need to create a query, and here we'll see how to import data from another workbook, and why. So, follow along. Here, we have survey results from a magician's conference, specifically the levitation session. The goal would be to get rid of these AVERAGE rows. Get rid of that empty column in column C. Just close some of these gaps and get one big block of data.
Get rid of these repeated headers. We would like to have the days of the week fill in entire column B. This is too much to try to put into a table. It would be easier to import this directly in to Get & Transform, so I'm going to completely close this and open another workbook. Here we are, and I'm going to get rid of these grid lines. Now, let's import that data. Go to Data, New Query, From File, From Workbook.
This is on my Desktop, and it's called Import from Excel File. Here is our Navigator, and let's click on Sheet1. There is a preview of our data. Now, the vast majority of the time, your selection down here is going to be Edit because you did this for a reason. You got to bring this data in so it can be cleansed. You typically not going to just straight load it, so let's go to Edit.
And here's our data ready for us to clean it up, and do things like right-click and get rid of that column here. Good, so now you've seen that it's possible to bring data in from another Excel workbook, and we did that because it was easier to clean the entire sheet than to try to put all of that data into a table.
- 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