Oz du Soleil shows how to query data from a table or range in Get & Transform in Excel 2016.
- [Instructor] Get & Transform uses queries as its source. Therefore, to use Get & Transform, we need to create a query. Here we have orders about pies, and in column D, there are holes in the columns, so we need to get those filled in, and it's best to do that in Get & Transform. And this data is in a range right now. It is not in table format. It's just data typed into cells. So let's click inside that data range, go to Data, From Table.
See, Excel is guessing where our dataset is, and it's guessed correctly. Notice the marching ants are covering the whole range, and we can say OK. There is our data. Now let's close and load this to the workbook. Now let's look at another way that this data can be set up. It might look like this. Notice that there is an empty column at F.
Let's click inside the dataset, Data, From Table. Notice that it is not guessing the dataset correctly. So let's cancel that out. Now we can manually put this into a table. Let's do that, highlight, Home, Format as Table, Table does have headers, OK. Now it's made that up, but we can get rid of that manually here in Excel, or we can get rid of it in Get & Transform, but the point is we can start a summary this way.
We can go to Data, From Table, and there's our data, we can remove that column, and then do our fill down to close that gap. As you move forward and get to know Get & Transform more and more, the typical way that you'll start a query is from a table or a range.
- 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