Import and combine data sets in Power Query - Microsoft Excel Tutorial

- [Instructor] A lot of times, we need to get data stacked up in one place. In this situation, we have a workbook and it's got data for four counties on it. Right now we're looking at Barco County. We've got columns for city, the year each city was founded, the number of high schools, the address for city hall and who the mayor is. And down in row 12 there's report ran by Cynthia. Okay, we don't really need that but let's leave it. Let's look at the other data. We've got Gladd county. Oh, report ran by Timothy. Notice also, there is no mayor column for Gladd County. And then Raddlit County. Mayor column but no report ran by data. And then Starling County. Let's go back to Barco County. There's a lot that we could do with this data if it was in one place. Maybe we want to sort all of the data regardless of the county by the founded date. Maybe we want to isolate the cities that have between five and 10 high schools, or maybe we just need it stacked up so that we can put it into a database. Now with this little bit of data, you could kind of get away with a copy paste. But I would advise against it because it is error prone. And especially if you have a lot more data than this. One thing you could do is put all of this data into tables and create four different queries and then append the four queries. But no, I'm going to show you how to import. First, I'm going to close this workbook. Good. Now I'm going to open another workbook. Now to get the data. In the data tab, get data, from file, from Excel Workbook and then navigate to where that workbook is called Counties Import. Double click. Now we are in Power Query's Navigator. We can do a preview and look at the data. Raddlit, Barco. And we even have that part about report ran by Cynthia. I'm going to click the folder there and then go down to transform data. Let me show you something. I'm going to click on table. See, there is all of the data down at the bottom for Gladd County, now, Raddlit County. Good. Let's look at this. On row one, Barco County and then table. That's where the data is collapsed. Then we look at the Item and Kind. We're looking at Barco County, which is a worksheet in that other workbook. And is it hidden? No, it's not hidden. Next, highlight the name, hold down the control key, highlight data. Right click remove other columns, because for our purposes we don't need 'em. We just need the data all in one place. Next, hit these diverging arrows. We don't want to use original column name as prefix and okay. And there is the data all stacked up in one place. If that's all we needed was to get this in one place, we would be done. But you can see that there is some clean up to do. So let's do some of that. A big warning. We have the column headers in row one and then repeated down here in row 13 and okay. They're repeated for every data set that we imported. One thing that we can do, okay. I don't need to have all of these columns highlighted anymore. All right. So I could go to use first row as headers. Okay, but look what happens. In the code, it's promoted to headers for city through mayor. That's fine, but it's promoted Barco County. This is fine, if we're done and there won't be any updates to the final data. We could go in and change the name of Barco County in that header to county. But if the data updates and there's a possibility that a county will show above Barco County, the query is going to bomb out because it's looking for Barco County. So I'm going to get rid of the steps, okay. Change type, and then promoted headers. And then I'm just going to type the headers. Now I've got all of the column headers correct. Now to clean this up. We don't need those header rows. So I'm going to hit this down arrow. And we don't need any rows that say city. Okay. Now we do have some years with missing dates. We have some high schools that have missing values. So we have to be careful about how we get rid of empty rows. Every one of the city rows does have a city name. So I'm going to go in this column and get rid of any nulls. Uncheck that. Okay. Next we have these report ran by values. I'm going to filter this. Filters, begins with. Keep rows where city does not begin with report ran. And hopefully you won't ever have a city name that starts with report ran. Okay, those are gone. We've got all of the data all cleaned up. Let's sort the year founded column descending. Okay, so West Farkas was founded in 1984. And then we go down. Port Cloyer was founded in 1837. And then we have five cities where the founded date is missing. Okay. We got what we needed. We can close and load. There's the data all in one place sorted by the year founded for each city. And we did not do a fragile copy paste.