This exercise consists of the instructions file, which you see here, plus three supporting data files, which will want to connect to. At the end of the exercise, we'll have four different output tables on two sheets as per the instructions. Let's start creating our solution by connecting to each of the data sources. First, the quickest and easiest is the CSV file type. So I'll start there. To create this connection.

I'll go to data, new query from file and I'll choose from CSV. I'll browse to my exercise one CSV data file, choose Import. And now I can see my data. I'll go load load to, I'll only create a connection. I now have the connection for my CSV file. Right.

Now let's move on to the Excel files. Again, I'm going to go to new query from file and we're going to choose from more Look, I'm going to use the exercise one XLS x data source, I'm going to go to import. Now, I do need multiple items. So I can select this and then select the two different ones I wanted per my instructions to get the cleanest time entry data available. So I can look at this time entry data sheet. It's got a lot of extra columns.

And I can look at this time entry data table. And it looks pretty clean, so I'm going to use it. The other piece of data I wanted was the most substantial employment information. So I'm going to look at the employment sheet that looks pretty substantial. But I've also got this employment officers, the employment officers only asked for items. So I'm going to go with the sheet.

It's Messier, but it has more data. Now that I've got my two components, I'm going to go to load two, and it's going to evaluate both of them. I'm going to load this one to create a connection. And now both of them appear in my workbook queries individually. connections, the last data sources in a small Access database. To connect to this again, I'm going to go to a new query from database.

And from Microsoft Access database. I'm going to browse to exercise one ACC DB data source, and I'm going to import it. This brings up a whole list of queries, it's fairly daunting. So I'm going to search for the name that I want. I gave you a specific name here. So I'm going to go see base pay, and you can see that as I'm typing it in, the table appears, I can select it to see what the data looks like.

And I'm going to choose load to and again, I'm going to send it to a connection. I've now got all four of my data connections specified. And since only this instruction sheet exists in this workbook, I'm going to start loading these into worksheets. I want the two XLS x data sources in the same workbook. Now there are different ways to access to load two again, the first one that I'll generally use is I'll right click and choose load to I'm going to choose table, they're going to choose new worksheet so that gets added to the to a new worksheet. Now it's on to our next data set, I'm going to select cell h1, then I'm going to click on the employment, and I'm going to hover over it to get a preview what's the preview appears, I'm going to go to this dot dot dot and choose to load to hear put into the table.

I'm going to choose existing worksheet, it's going to be populated with the cell that I have selected, I'll press load, this immediately pulls that data and I'm going to do the same now for my CSV except I'll right click Load to table new worksheet and load. I'll scroll to the right a little bit. I'll select cell a4, a B one z base pay schedule, right click Load to and again, I'll select table and an existing worksheet and load. I've now loaded each of the datasets into my workbook. So as you can see, the process of creating the initial links is very straightforward and quite easy. And once they're created, they're all identical in form and function.

Once you bring it from variety of data sets together into Excel to get and transform, a whole new world of options will open up. In our next chapter, we'll break the ice on some of the most frequently used and easiest features to apply