digest
Welcome to BI348 class video number 30. If you want to download BI348 Chapter02.5 Import01 TextFiles, or you want to download the zipped folder Import-01-TxtCvsFiles that contains these text files, click on the link below the video. Now, I've already done this exact example, but I did it in Excel 2013. I just want to show you in Excel 2016. That's a brand-new version that just came out less than a month ago. I want to show you where Power Query is. In earlier versions, you had to download it, and it would show up as a Power Query ribbon tab. But now, we simply go over to Data, and there it is: Get & Transform.
Since we have text files in a folder, we want to go over and click New Query, From File, and From Folder. In videos 28 and 29, we did exactly the same thing: From File, From Folder. But this was actually a button in the Power Query ribbon tab. When I click this, it looks like the same exact dialogue box. We're going to click Browse. I'm going to browse to find the folder that I downloaded and unzipped. Inside of it there's Start Files. The way From File, From Folder works is we're telling Power Query to always look in this folder. It will try to import all of the files, so when I click OK, and then click OK, the Power Query Editor looks exactly the same.
Now I'm going to come over here and be sure to give it a good name. I call it Import Great Data from Text Files. Step one is that we have .txt, which is tab-separated data, and .csv, which is comma-separated. I only want the text, so I'm going to click the drop-down and filter out the .csv. Now this query is programmed, as you can see in the steps over here, to look in that folder and only get file extensions .txt. Check this out: if we were to dump more text files in that folder and refresh inside of Excel, it will automatically go out and get those. Right now we only have two files.
We don't need any of this. Right-click Content and choose Remove Other Columns. Now we come to our double downward-pointing arrow. Click to expand it, and it did a few steps here. If we look right here, Import CSV is really just the term they use for whatever type of text data. Notice that the field names at the top of each data set are being treated as a record. But it automatically did this step, Promote to Headers, and then changed the type. Right here, this should be text. Right here, this should show up as decimal numbers. That's pretty cool.
Now we have to be careful when we're importing from a folder. We have to make sure the actual field names come from the right file, because this is just from the first file. There's one other file way down there that has the field names listed as a record, and I don't want that. So we have to pick one of the columns, and I'm going to pick the column that has the fewest unique records. I'll click the filter. If you have big data sets, you'll see Load More. You always want to click Load More, because it will go through and find the unique list from all of the files. We see our list here, and it's complete. Notice Course ID. Of course, the first file promoted it, but that one record further down has the field. When I filter this, it will filter out the whole record, which means the actual three field names from that second file. I'll click OK, and there it is, all of our steps. We have a name.
Now we can go to Home, Close and Load. You don't want to do that unless you just want it automatically on the new sheet. I'm going to have complete control, so I click Close and Load To. We're going to dump it as a Table. You could do Only Create a Connection and then dump it into the data model. We did that in video number 29, but here we just want to dump it as a Table. I'll click Existing Worksheet, A1 is fine, and click Load. Just like that, it goes and gets those text files and dumps them right here.
Back in video number 28, we took this example and built a dashboard, then added new files and refreshed, and everything updated. The point of this video is just to show you that Excel 2016 has no problem with it: Data ribbon, Get & Transform. That is Power Query. All right, we'll see you in the next video.