Rather than jumping into using data from external sources, we'll start this course by building the query from data stored within the local Excel workbook, along with discussing the options available to us for saving those queries. in some senses, using local data is harder than using external data, since you have to prepare the data first. By the end of this lesson, he'll be able to recreate this simple file. I have a simple data set with three columns and four rows, and each column has its own label. Initially, this data will be unavailable to the get and transform tool. The first step in order to use data within a workbook is to convert that information into a table.

To do this, I'm going to select the data range including the headers, and then I'll go to the Insert tab and click the table button. A prompt will appear asking you for the range with the data. This will default to your currently selected area. Additionally, you'll see a checkbox that asks whether the table has headers. Since our table does have headers, I'm going to make sure that it is checked. If you do not check this box Excel, add a new row to the top of the table with some generic column header names.

Upon pressing OK, I now have a table. I generally recommend renaming your table on the Design tab at this point. For this table, I'm going to rename it to query data source. Once the data table has been created, using it as a Power Query data source is incredibly easy. Select any cell within the table, go to the Data tab on the ribbon and choose from table. This will immediately open the query editor and populate it with the data from that table with We've just created our first query.

Now that it's created, how can we save it and reuse it? First, we need to give our query a name. In the Properties box to the far right, the name will default to the original table name data query source. I prefer to use different names from the tables since they're usually transformed in some way. In this case, I'm going to change it to local data underscore query. And with the name in place, I'm going to go up to the top left, and click on the bottom half the Close and Load command to choose Close and Load to from the available options.

This will close the query editor and launch a new prompt. If I had selected just closing load, it would have used the default settings of table in a new worksheet. Within the prompt. The table option will tell Excel to display the results of the query in a table format, like the one that contains our source information. The resulting table will contain a refresh commands that can pull new data based on any changes to the underlying information. If you're unfamiliar with tables, I highly recommend learning about them and their features.

The only create connection option tells Excel to save the query as an accessible connection, but not to publish the information out to a table. This data will not be accessible by formula, but it can be accessed by other power queries and later on, we'll see how we can load it into a table. The select location options are only available whenever the table option is selected. Otherwise, they're disabled. If you're familiar with PowerPivot, the final checkbox will allow you to load the data directly to Excel data model. In this course, we will not be using this feature.

For now I'm going to create only a connection and press load. creating our first query will launch the workbook queries content pane along the right side of Excel This pain could be open manually from the data ribbon in Excel by clicking on the show queries button. This bar has some key information listed. The first item you'll see is the query name. Below the name, you'll find the query status, which tells you whether the query is loaded to a connection, or the number of rows that have been loaded to a table within the workbook. Our query shows that it's only a connection with no other details.

If we hover over the query, it'll show you a preview of the query and a little preview window with a ton of additional information. Since our query is small, we can see the entirety of the results set, but you'll usually only see a very small portion. It will list the columns, the number of columns and the last time that it was refreshed. It will also show the data sources and a few additional commands at the bottom. The commands in this window read from left to right view and worksheet edit dot dot dot and delete the data dot dot button has a list of other options within it. From those options, our only interest will be in the load to command for the time being, and we'll cover the others later in the course.

For queries that are connection, only, the view and worksheet button will be disabled if it had been loaded into a table. This would allow us to navigate to its location within the workbook. The Edit button will bring our query editor up so that we can make modifications to the query, the delete button deletes the query altogether. The load to command within the expanded menu will let us change how our query is saved at any time. Since we're here let's change our current query to a table loaded into a new worksheet. Once I press load, it'll take me to the new query location and load the query into our worksheet for use.

Once I've done this, our query panel update to reflect the new load status. Instead of seeing only connection. We can now read that four rows have been loaded. We've created our first first query linked to a data source, and we saved it and pulled those results into our workbook. In our next lesson, we'll incorporate some different data sources by creating queries that link two different Excel workbooks, to an Access database to a congress separated values file, and finally, to a SQL database