digest
Welcome to Power Query video number three. If you want to download this file, as well as some CSV files that go along with this video, click on the link below the video. In this video, we want to see how to take multiple CSV (comma separated value) files from a single folder and import them using Power Query. Power Query is a free download for Excel 2010 or 2013. You go to Google and search for "download Power Query." It's easy. Now let's check this out. Power Query is going to allow us to simply point to a folder. It will go and get these files and combine them into a single table. But check this out: later, when we dump new files into this folder, if we refresh Power Query and our pivot table, everything will update. It will be absolutely amazing.
All right, now let's go back over here. We're going to start by going to Power Query, From File, From Folder. We have our Browse button. We click it, and we can now navigate to the folder. Inside that folder are three CSV files. I'm going to click OK. Now I'm going to click OK a second time. The Query Editor opens up. I want to come over here and name this "All City Data" and press Enter. Now we have a bunch of columns based on information about those files. For this video we don't need any of this. In later videos we'll see how to extract some information from, for example, the file name. All we want is the Content column, so I'm going to right-click and remove other columns. These steps, like "Remove Other Columns," are listed here. You can go back through the steps at any time. It's remembering them, and those steps will be saved. That's how it knows how to do this later when we refresh.
Now let's go over to View, Advanced Editor, and check this out. You can watch this being written. There's the Source folder path, and so far all we've done is remove other columns based on the Content column. Now I want to click this double downward-pointing arrow to expand the table. Right off the bat, it looks like it got the field names from the first table. We're going to have a problem with some of the field names from the other tables. But first, I want to click on City and go over to Data Type. I want to check each one of these columns, because sometimes when you're importing, it will treat a date or sales as text. I see that it's interpreting them correctly. Look at that. This one, I'm going to actually change to Text, because it's never going to be used as a number.
Now let's check out whether we can figure out how to get rid of the records down here that show the field names, because we'll get an error. It'll be text, text, text, and you can't have a date and text in the same column without getting an error. So let's click the drop-down. Here it says "List may be incomplete." I'm going to click "Load more" and check that out. It sees down there somewhere that there's City. Remember, I'm going to press Escape. When it sees City here, it will also see Date, Sales, and Store ID. We can do that over here as well. Just to check it out, load more, and sure enough, Store ID is in this one. We won't be able to do that little trick on these, because they're dates and we'll get an error. But let's just filter this. When I select City, it writes into the query everything that's not City, and this will get rid of our field name records, which would cause problems. Click OK.
Now let's go look at View, Advanced Editor. All right, so we have Combined Binaries. Check this out: there's a CSV.Document function that you can actually use directly. In later videos we'll use that. Then there's Table.PromoteHeaders, and then Table.TransformColumnTypes. Check this out: it got the first row as the header, City as type Text, and each one of these columns has a field name and a type. Then there's our Filtered Rows, not City. I'm going to click Done. I'm going to go to Home, Close & Load To. Now, Table, New Worksheet. That means we're dumping into Excel. If I want to create a connection only, and if you're in 2013 you can add this to the Data Model, we're not going to do that. I'm going to choose Table and uncheck "Add this data to the Data Model," and then Load. You can check out the query. It's going through all the rows, all the files, and there it is: 664,000 rows. If I press Ctrl+Down Arrow, Ctrl+Home. Now let's build a pivot table, because this is going to be amazing. We're going to have this awesome data source here that will just update, and it will instantly go out and get the new files we put in there.
All right, so Pivot Table: Insert, Pivot Table, or the keyboard shortcut Alt, N, V. I'm going to put it on this sheet so we can see it all in action. I'm going to put it in F1 and click OK. Store ID goes down to Rows, Sales goes down to Values. Right-click, Number Formatting, not Format Cells. Currency. With millions, we don't really need to show decimals. Click OK. Design, Report Layout, Outline or Tabular. I'm going to select Outline and close the Field List. Now I want to add a slicer. Whoops, right-click, Number Format. What happened there? Oh, I clicked Number instead of Currency. Click OK. That's much better. Now we want to go to Analyze and add a slicer for City. Click OK. The cool thing is that we're going to get some new data from cities. We're just going to refresh the pivot table, the slicer will refresh, and everything will refresh.
All right, I'm going to save this with Ctrl+S. Watch this: right-click, paste. I'm adding two new files, and I'm going to do this in two steps. If anyone knows how to do it in one step, you know, I always use, or I like to use, Data, Refresh All. The keyboard shortcut for Refresh All is Alt, Ctrl, F5, which I keep trying and hoping the pivot table will update. Ctrl+Alt+F5. You can see over here that it's working for the query. Wow, look at that: 800,000 rows. We're done. Right-click in the pivot table, Refresh, and boom. Check that out. That is just amazing. Power Query is going to go and get multiple CSV files, build a pivot table, add new files, and instantly everything updates. All right, we'll see you next trick.