digest
Welcome to Power Query video number six. If you want to download this file or any of the other files, we have XLSX files. There are five data source files you can download; click on the link below the video. If you're not hanging out with Miguel Escobar at his blog or at his YouTube channel, you are missing out. He has been publishing some amazing Power Query tips lately. Now we're going to take some of Miguel's awesome tips for importing Excel workbooks in this video and see if we can import some workbooks, build some pivot tables, and then update the folder with all the files and see if everything will update.
Over here, we're going to start with a single folder and three Excel files. Later we'll add some more Excel files and see if it all updates seamlessly. Power Query is the key here, and you have to download it. It works in Excel 2010 or 2013. We're going to go to Power Query, From File, From Folder. The last couple of videos we've been doing this with CSV files and text files, but here we're going to browse for Excel files. There's the one folder in there with Excel files. We're going to click OK, and click OK again. We get a bunch of columns with information about the files. I want to come over here and name this "Import Excel Files." The only column we're going to need is Content, so I'm going to right-click and choose Remove Other Columns. This is a binary file, and we need to extract all of the Excel information out of it. So we're going to add a new column: Add Custom Column. Highlight this, and we'll call this "Get Excel Data." Check this out: we're going to use Excel.Workbook, open parentheses, double-click the Content column, and the Excel.Workbook function will extract the Excel information from the Content column. Click OK.
Now we don't need Content anymore, so right-click and Remove. We're going to expand using that side-to-side arrow, click OK. Now there's the data we want, but the problem is that if we look at this data, it has column headers in the first row. So we're going to add another column: Add Custom Column, and this one I'm just going to call "No Headers." Check this out: Table.PromoteHeaders, open parentheses, and it's the data we want: Excel Get Excel Data.Data, double-click, close parentheses, and click OK. Is that a totally hot tip? Check this out. Thanks to Miguel Escobar, look at that, what a simple little formula. Now we don't need any of these other columns, so I'm going to right-click and Remove Other Columns. Now let's click this, and check this out: we can uncheck "Use Original Column Name as Prefix," and it will give us the actual field names as our headers. That's another tip I got from Miguel. I didn't have the latest version, the November update for Power Query, and that just came in. Click OK. You have to be kidding me. Boom, look at that. Here are all of the steps. We can come over to View, Advanced Editor, and there they are. Click Done. Now we can Home, Load To, and we're going to dump it right on a new sheet as a table and load. I think we're going to have some errors here, but we can go back and fix them. You can see the date; I didn't import it as a date. So as soon as this comes in, I'm going to double-click to edit this and check each one of the data types. Any is fine. We'll set this one as Date. Sales has a decimal, and Store ID I'm going to change to Text. There's the new line added in our query. Save and update here, and check that out. There we go.
Now we're going to make a quick pivot table so that when we dump some new files in that folder, it'll all update. I can go to Insert, PivotTable, or Alt, N, V. I'm going to put it on an existing sheet just so we can check it out in action. F1, click OK. How about we drag Sales down to Values, Store ID down to Rows. Right-click, number formatting, maybe something like Currency, click OK. Design, Report Layout, Outline. I'm going to do Outline. Analyze, Insert Slicer, and let's add City. We'll drag this up, and I'm going to leave some extra room so when this updates, it'll be amazing. We get the new city names here. Now let's save this.
All right, now I'm going to go over to Windows Explorer and add two new files. There we go: San Francisco and Tacoma Excel files. Now we go back over here, go up to Data, and refresh all. Over here the query is updating. The query has updated. Control, Down Arrow, we've got some new records. Control, Home. I still can't figure out how to do it all in one swoop, but right-click, Refresh the pivot table, and boom, look at that. That is simply amazing. So we had all those files imported through Power Query and into a pivot table. When we put new files in that folder and refresh, everything updates. I love hanging out with our awesome online XLS team. We'll see you in the next video.