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.
AuthorOz du Soleil
- 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
Skill Level Intermediate
1. What Is Get & Transform?
2. Working with Queries
3. Working with Columns
4. Working with Formulas
5. Pivoting and Unpivoting Data
Group data2m 31s
7. Appending a Query
8. Merging Data with Joins
9. Putting It All Together and Applying Your Learning
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.