Excel Power Query #03: Import Multiple Text Files in 1 Step, and Amazing Pivot Chart For Grade Data

welcome to a power query video number three hey if you want to download this workbook and follow along click on the link below the video hey now this is power query video number three but I didn't do a one and two I actually renamed two of the power pivot videos now I actually want to go over and look so when you click on the link below the video you get to this page and you got to scroll down this has got huns of files so I just want to scroll down and look at here's the power pivot series with all the workbooks and it was a video eleven and twelve which actually turn out to be number one and two for power Creek there's the files now when you download this there's an empty workbook with a few things and then here's what we're going to do we're going to see how to take three text files and import them not in one to three steps but all in one step now the more I learn about power query the more I am amazed maybe it's not power pivot or pivot tables that's the most amazing feature in Excel but power query now that's not really true right they're all do they're their own thing but power query is profound in how it can import transform clean data and bring it into Excel or power query now we want to go back over to our workbook because I got to give some shout out to Bill sis's Miguel Escobar and Ken Paul's these guys are helping me to learn power pivot here's the links you can go check out their stuff Ken Paul's is a Excel MVP with tons of amazing thing as his blog Miguel Escobar has amazing power user videos he's been doing power user videos at YouTube for a long time much longer than me and Bill he doesn't usually post videos but he puts amazing comments below my videos and is one of the smartest Excel guys I know all right so let's check out how to do this whoa wait a second I want to show you the end result because I want to build three pivot tables and build this report here this is grade data and we have three classes we're analyzing way back in time all the way to today and we want to figure out the mean standard deviation and count we're going to build three pivot tables but I want to summarize this visually in one chart so we have a slicer over here we can click on any one year and instantly see how the data has changed all right let's go over and import our data the first part of this is going to be power query the second party and we'll just be straight pivot tables and charting now power query you got to go and download this just Google download power query it works in 2010 and 13 now here's the amazing thing from file a wait a second from folder this is amazing it'll allow us to import easily text files all in one step now let's actually go look at our files to understand how this feature will interpret it in one step this is just straight tab-delimited records in rows each one of the files is like this so the importing using from folder will just automatically know to slap all three tables together in later videos we'll see where if we are doing a different type of data import either from text or from Excel files it won't be as easy as a single step here we are power query from file from folder here's our browse button we go to wherever our folder is and the final folder here inside that folder there's no more folders there's only those three text files I'm going to click OK click ok Wow check that out it actually brought information and attributes about the actual files but it's this binary one that has all the data now we're going to delete all these so watch this I'm going to right click and say remove other columns now I can simply actually let's rename this before we do anything else all great data tab and check that out that little arrow arrow let's click and instantly Boop there is our data set now we imported these from text files and it interpreted the first row as header or field names that won't always happen but there's another thing is that when we imported these down below and these are huge data sets these field names can show up so I want to click on the filter and this is an amazing feature a filter everywhere filter pivot tables power pivot power query it gives you a unique list of items that you can check but check this out list may be incomplete I'm going to try and load more and there it is there's that field name way down there somewhere now you can run you'll prove this to yourself and run a little teeny data set and then you can look through and see that in fact the field names often do show up but I'm going to uncheck this this becomes part of the query I'm going to click OK you can see there's a bunch of steps over here of our query all right now our data is good to go we could actually come over and look at each one of these write lists may be incomplete and we can see that no way look there's all of our numbers we could also come over to year and select list may be incomplete I'm going to load more and sure enough there's all the years click ok now we can simply close and load - we are definitely going to load it to a table in Excel if you wanted to load it to your data model there it is I'm going to put it on the existing sheet click the collapse button a 1 click ok click load you've got to be kidding me that is absolutely amazing control down arrow there's our 75,000 rows of grey data for these three classes control home now we're going to build a pivot table on this other sheet insert pivot table or we're going to use the keyboard Alt + V since we're putting it on a new sheet I'm simply going to hit enter here's our field list class down to rows gray down to values here's our first pivot table I'm going to close this field list we don't need any more right click and I'm going to the one-stop shopping for pivot tables value field settings I want to change the name actually I want to change the at the calculation first now we're going to change this to GP a and we want to add some number formatting I want to show just to one decimal number decimal click ok so one two three our one-stop shopping we change three things click OK now let's go ahead and go to analyze insert slicer Year click OK and we could format this if we want four columns right to the edge however you want to do it now I'm going to come over here and I'm not going to change the row labels because we're not going to really look at this or we're print it out we're just going to make a chart but watch this I'm going to ctrl asterisks to highlight the whole table control-c control-v now I want to right-click one-stop-shopping and we want to change it to standard deviation of the sample number formatting we're going to actually put a bunch of decimals here I'm going to include 4 decimals click OK now I'm going to come up here and name this SD okay control asterisks to highlight the whole table control-c control-v click inside the values right click value field settings and now we want count we want to count the numbers we'll change the name and now we'll do number formatting I'm just going to include 0 click OK click OK so now we have our three pivot tables and now we can already see that it's working you know what I'm going to change right here right click and since I don't need to one-stop-shop I'm going to go to number formatting and increase it to two decimals click OK now let's create our chart and the tricky thing is actually going to be the label that we want on the top but we'll see a cool excel trick I'm going to use the keyboard for the default chart alt f1 my default chart is a column chart hey that's looking pretty good already I'm going to click up here now actually I just got an idea I want to actually come back over here to the pivot table right click show field list I want to drag here to the filter which is basically this similar to the slice of the slicer is just a fancy visual way I'm going to close this but the reason why I want that there's because I want to make a label up here and I'm going to do a few different labels for this chart where the labels are going to be based on formulas from the cell so I'm going to say label colon and our formula will be equals student data in space at the end double quotes and join it now if I just click on this cell it'll give me to get pivot table data and I'm just going to type b1 alright or anything so when I change the year over here my label changes so now I'm going to click on the title either click up on the equal sign or type equals and click on Athlon so there we have student data in 1980 now the tricky thing is I'd like to pull the data one two three from three different pivot tables and I want to link these data labels to the actual cells and that's an awesome new feature in 2013 all right so I'm going to type data label one I like to label everything so I know exactly what's there and I'm actually going to color these green these are not really part of our anything we're going to print out I like to indicate that this is a formula with some color just so I don't make a mistake later right now I know that changes the chart will will mess with the chart later but let's make our data labels because I want one two three I want the GPA the standard deviation and the count so GPA equals with the space at the end and I'm going to click in now I'm not going to click on before I want to type before because it will give me that get pivot table data function night I don't want that now I'd like to have all three here and in order to show up nicely in the chart I actually need to put a hard return in my formula one two three listed vertically on top of the column so I'm actually going to use the character function character hey we know our ASCII characters there's 255 of them and ten is actually a hard return you've got to be kidding me now let's join the rest of it now here's the rest of it standard deviation equals b10 that's actually in the second pivot table and then character 10 and count equals and we're looking down at the third pivot table now we're going to run into a problem with number formatting control enter formulas cannot see that number formatting so I have to hit f2 and we're going to put the text function the text function will convert a number to a text string given a custom number formatting in double quotes zero point zero zero that'll show two decimals and double quote close parenthesis we'll have to do the same over here so I did the same thing here I'm showing three decimals because I want a little bit more on the standard deviation we don't have to add text because that is a whole number let's see what this looks like control enter all that's looking great now we could go ahead and add word wrap to see that it's working here but we don't need to control Z because the formula that will connect these labels to there will understand that character function we'll copy this down now let's go to our chart data labels that click once control one to open up the task pane and we're going to uncheck values which will remove them and now we're going to do values from cell absolutely amazing here's our data label range we simply highlight click OK now that sort of looks like it's working there I do want to do something to indicate that GPA is actually the column and SD standard deviation and count are different I'm actually going to come up to our formula f2 and I'm going to do something totally crazy I'm going to actually add another hard return so copy ctrl V so there's two characters ctrl enter and double click and send it down alright so we could do this various ways but GPA is actually the column this is extra data Wow check that out let's go ahead and close this and guess what we don't need the pivot tables or our formulas I'm going to click on the edge of the chart to lodge this up in the corner now let's get our slicer and you got to be kidding me look at that absolutely amazing ok so in this video we did some fancy charting some fancy labeling with formulas there's some pivot tables under this chart a slicer we even started back over here using that amazing power query from file from 4 older all right we'll see an ex trick

Channel: ExcelIsFun

Views: 50,456

Rating: 4.9137931 out of 5

Keywords: Excel Power Query, Excel 2010, Excel 2013, Mike Girvin, Michael Girvin, Mike excelisfun Girvin, excelisfun, Highline College, Data Analysis, Import Data, Transform Data, Merge Data, Append Data, Import Multiple Text Files, Tab Delimited, Power Query Import Multiple Files, From File, From Folder, Chart, PivotTable, Student Grade Data, GPA, 3 PivotTables, Pivot Table, Text Formulas, TEXT function, CHAR function, CHAR 10, Hard Return in formula, Chart with Custom Labels

Id: QogBqCx_EgY

Length: 15min 8sec (908 seconds)

Published: Sun Nov 23 2014