Excel Magic Trick 1338: Power Query: Unpivot 12 Cross Tabulated Tables into One Proper Data Set

welcome to Excel magic trick number 1338 hey if you want to download this Excel workbook 1338 start file or the finished file so you can follow along click on the link below the video hey this is going to be a great video power query how to take twelve cross tabulated tables and convert them to one proper data set now we have two examples on this sheet we have actual Excel tables so the person has the products listed here and then the dates and in each column are the units sold this table is for January all the way down to December we can see that there's a different number of columns for each table down to December down at the bottom now on our second sheet over here we have the same data but they haven't been converted to excel tables now both of these cool tricks come from bill sis's at YouTube truly a power query artist and poet when it comes to awesome efficient power query solutions now in our first example we're going to use excel dot current workbook function and this function will actually take all of the objects from the current workbook now we have Excel tables but we also have some other objects like defined names and print ranges that we're gonna need to filter out so if we go up to the name box and click the drop down we can see that all of our tables from January all the way to December have been consistently named but right here that is a defined name if I click on that we jump and see the define name if I select the print area boom that's the print area the print range the define name and all of these tables or objects that Excel current workbook will import alright let's see how to do this and by the way all of these tables are on one sheet they can easily be on any sheet or individual sheets all right in two and sixteen power query is on the data ribbon tab there it is get in transforming in Excel 2013 or 10 you have to download it and install power query as a separate tab we need to go to new query down to from other sources and then blank query it immediately opens up the query editor the formula bar is selected and we can type our function equals Excel dot current workbook now power query functions require that you spell it correctly Slyke Excel but you also have to have the correct case when I hit enter it should pull in all of the objects as long as I've spelled it correctly now here's all of the names here are table names but down here there's the define name in our print range now if we were using Excel workbook to go outside this workbook to get objects from a different workbook we would have something called a kind column to filter by object but here it just gives us the name no problem we're gonna assume that these are all tables and we're going to use the table dot unn have it other columns functions which will search through all these objects to try and find a particular column these objects down here will not have that particular column and so they'll get an error and then we can use our filter to filter out the errors which will filter out the objects we don't want now the very first thing I do before I add this extra column is guess what we come over here to properties and we're gonna name this now I named it all CT for cross tabulated tables into one and enter now there's two things that are important about this name the first is that's the name of the query so if we need to come back and edit it later that's the name of the query but also this name will be the name of the table when we get our final proper data set that will be loaded to excel well wait a second if this will result in a table loaded to excel guess what Excel doctor workbook will try and import it into our query if we didn't take that into account our table would always be doubled this is called the recursion problem so guess what these are the table names right and this is going to be the name of the table that results so we're gonna very carefully copy this and we're gonna come over to the name column the filter for this column is gonna be does not equal and there it is does not equal and I'm gonna control V so it's not going to equal the name of the resultant query table I'm gonna click OK we can see there's a step over here now we're ready to add our extra column to unpin each one of these tables now let's go over here and look at each one of these tables you don't want to click on the name you want to click right in the white and check that out there's a preview down here now notice the crosstab product date there's the product names and here's the date what does unpitied do it'll take the column headers which are if you think of this in terms of a pivot table that's the field we drop here the date field and it gave us exactly one of each date so unhip it will take all of these dates roll them up into a single column and we'll call it date and then it will take all the individual units and put that into a third column that will call unit each one of these tables if we get a preview we'll get unlimited if we come down to the bottom and look here well that is not a table look at that neither is this one now notice that there are some automatically created column names column 1 column 2 that'll become important all right let's add our extra column we go to add column add custom column our new column name will be I called it unfit each table now we're gonna have to use a function table dot unfit other columns open parentheses again hopefully I spelled everything right and got the case right now there's four arguments we're gonna use in this function the first argument is hey which table do you want me to unpin it well guess what if I double-click content if you look over here that's the name of this column and for each record there's a table so that will work comma the second argument is the field that we do not want to on pivot now if you've ever used the actual feature on pivot other columns instead of typing out this function you know that you can highlight as many columns as you want and then say unfit the other columns well in our case we only have one and I'm going to put it in double quotes it's product slash date now we have to put the field name in double quotes but double quotes alone will not work because this argument allows you to have one or more columns to not unpin it so we actually have to put it in lists syntax lists syntax is exactly like an array constant if we were over in Excel you have to use curly brackets so when we use curly brackets in power query it says this is a list now for us it's just a list that only has one item but that argument requires that so that's the second argument the column to not unpin it comma then we have two new columns now the first column is going to be date so let's call it date and we put it in double quotes then we type a comma and I'm actually gonna add a space after each one of these just to give us some breathing room the second column will be called units in double quotes now we can close this off so we have our four arguments the first argument what table do you want to unpin it the second argument as a list is the column we do not want to unpin it the fourth argument is the name with all of our dates and the fourth argument is the name of the column that will contain our values our in our case units now I'm gonna click OK and there is our new column let's preview I'm gonna click in the white off to the side not on the word table and there's our unfitted table there's the column we didn't unpin it there's the date there's the unit's if we go back and look at our cross tabulated table it went from this to this and down at the bottom look at that there's our error and here's the error message it could not find the product date column because of course this didn't come in as an Excel table so it created these column one column two column three it searched through those and could not find products last date now we can come up to the top and we need to get rid of these errors but notice there's no filter button no problem we can remove heirs home reduce rows remove rows and all the way down at the bottom we have remove errors or we can right-click remove errors now this is the only column that has the data we want we don't need these columns right click remove other columns and now we can use our expand button I'm going to click date product units we want to uncheck this we do not want the original column name as a prefix for these uncheck click OK wow that is amazing now we have a little house keeping that date name is fine but that little icon abc123 says hey it doesn't know what datatype so come up to dad I type the drop-down date come over to products last date double click we're gonna call this product enter we will add a data type drop-down text units is a fine name but we need down at I whole number and there we go there is our table three columns completely unlimited correct data types now we can go up to close and load close and low to now we actually don't need to check this if I want this table to be automatically placed on a new sheet I can just click close and load and there we are we can see our query over here I'm gonna come down here double click and call this proper data set from Excel tables so we went from a bunch of cross tabulated tables into our proper data set now let's check this out here's what's so cool if someone really liked this method of storing data we can actually change any of the numbers in here or we can add an extra column I'm gonna go down to the table for November and notice it says Monday Tuesday Wednesday well actually we had at Christmas time we sold on Saturday and Sunday we'll watch this I'm gonna highlight those last three columns use my move cursor and move it over to columns instantly it adds two new columns in the middle of our table I'm gonna call this 11th last 26 tab 11/20 7 tab and then I'm gonna hit pause and type some numbers here and so now these two columns are part of this table but remember this is a table object when we imported it into our query it's looking at the whole table so when we go back here and refresh these will be included I'm gonna go over to the sheet right click refresh now over in our workbook query window we can see now it has or Rose if I scroll down somewhere down in the table I found for example these two new records for the quad boomerang on the dates added that's pretty amazing control home we have our proper data set we can make whatever pivot table or sorting filtering that we want now in this example remember we had 12 Excel tables let's go look at a slightly different example on 12c tea tables look at that our tables are not already converted to excel tables now no problem if you wanted to you could convert each one of these to an excel table and then do what we just did in our first example but this trick comes straight from Bill's scissors let's just convert all of the 12 tables into one single table and then over in power query we will coerce individual tables from our one large table now we need to convert all of this to one table so I'm gonna click in the top cell and I'm gonna use the keyboard ctrl shift end and it gets us to the last possible cell that we've used which is the last column last row in our December table now I'm gonna use the keyboard ctrl T to convert this to a table and I want to make sure to uncheck my table has headers we're actually going to need the dates as records in our one large table I'm gonna click OK now you see that it gave us column one two and so on that's fine because again one of the tricks we're gonna need over in power query is a marker that says where the Cross tabulated tables start and it will be our date now we have one big table we can go up to data and use from table that will import it into our query editor now of course the fundamental problem here is this is one table this is another table so how are we going to actually fund each table what we're gonna do a bunch of tricks and it's gonna be based on this first column that contains dates now what I ultimately want to do is get that date and fill it down notice it's 1 1 2016 this one's 2 1 2016 so I want to fill these down those will be the markers for all of these records we can use group by to group them together as a table but first we have to figure out how to fill this date down in order to fill down we're gonna need nulls everywhere so watch this we're actually gonna highlight all of the columns I'm gonna select the first column go all the way to the end before I click on the last column I'm going to hold shift and I'm gonna convert all the data to data type text that will help with some of our tricks in particular we'll have to use the date parse feature and it will require that these are texts now I'm gonna copy this column because I need to retain that date and these numbers for our final table so I'm gonna right click duplicate column it throws me all the way to the end I'm actually gonna double click this and name this date grouper because this will be the column that helps us group all the records together now I can go transform and in the date and time group there's a drop down for date and parse by the way if you can try this without having converted that to text and it wouldn't work and in fact that screen tip right there tells us returns a date value parsed from the text all right so we're gonna click parse and luckily it only got that one date here are some errors those nulls will be fine but we need these errors to be knows if we're gonna use the fill down feature no problem I can change the errors to no transform any column replace values replace errors or I can come to the top of the column right click replace errors and I'm going to replace these with no all is sort of like an empty cell in Excel but it's not it's an undefined value which says there has been nothing entered for this particular cell I'm gonna click okay now we have our nulls now we can fill the dates down come up to transform any column fill and fill down or we can right click fill down and look at that now we have a marker that says all of these records here belong to one one we can now use the grouping feature with the column selected transform or home we're gonna find group by because we had date group or selected it wants to group by that here we're using group by just to gather up the records many of the other videos I have done we use group by for an aggregate calculation but here we're gonna name this column all records for table and our operation is going to be all rows this just gets all the rows every single one of them there's no column here because we're not making an aggregate calculation when I click OK this is gonna be amazing when I select off to the side not right on the table but off to the side look at that there is our cross tabulated table now unlike our example we did earlier in the video however column one column two we don't want those we actually need to promote these headers all right so we're gonna add an extra column just like we did earlier with an extra step to promote headers add column add custom column we're gonna call this promote headers and unpin it you know I always name everything explicitly that's not necessary we could have left it custom but I like to give it a good name now just like we did earlier we're gonna use table dot on pivot other columns open parentheses now of course I am such a bad type ER make sure that everything spelled correctly and the right case hopefully that's right all right just like we did earlier the first argument is going to be our table now I'm gonna double-click this that's the name of our grouping column but guess what remember those tables need to have the first row promoted to headers luckily there's another function called table dot promote headers open parentheses I mean certainly one thing great about power query functions is they named them kind of smartly alright so all we have to do is close parenthesis now that's the first argument of on pivot that'll be our crosstab table with promoted headers comma the second argument as the list and then in double quotes our field product date that will be the column we don't want to unpin it comma then we need to name our columns D comma and then units and double quote close parenthesis hopefully I spelled everything correctly when I come down here and click OK poem there it is we can get a preview you've got to be kidding me look at that now we don't need these columns here so right click remove other columns now we can expand I do not want to use original column prefix those names are fine click OK now I'm gonna scroll down here a little bit because remember we added an extra column and there it is column 31 that was the column that helped us group but we need to filter that out and if you scroll down there's lots of column 31 so when I come up to the top anytime we have filters whether in Excel or power query or power pivot anytime you select the filter to look at the list I'm going to say load more it gives us a unique list of items so I'm going to uncheck 31 click OK now we need to do some housekeeping date transform data type date tell click we're gonna call this product enter data type text units down a type whole numbers I'm gonna come over here and name this all records from one Excel table now remember in our first example since we used Excel dot current workbook we had to worry about the recursion but here we use from table so there is no recursion I can simply come up to home close and load and actually I want to just click this button up here cuz I want to send this to a new sheet click and there it is that is amazing I'm gonna come down here and name this sheet same thing I named the query now we have our proper data set so we could do something like create a pivot table insert pivot table or use the keyboard Alt + V I'm gonna put it on this existing sheet cell e1 deep down to rows units down to values design report layout showing tabular right-click number formatting number use a separator 0 decimal places click OK that is amazing so we saw two examples we went from 12 tables that were not individual Excel tables into our proper data set and then our first example we went from 12 Excel tables into a proper data set all right thanks to Bill scissors and power query we'll see you next video

Channel: ExcelIsFun

Views: 23,020

Rating: 4.9854279 out of 5

Keywords: Excel, Microsoft Excel, Highline College, Mike Girvin, excelisfun, Michael Girvin, Mike excelisfun Girvin, Excel Magic Trick, Power Query, 12 Cross Tabulated Tables Into One Proper Data Set, UnPivot Columns, Un Pivot Columns, Unpivot 12 Cross Tabulated Tables into One Proper Data Set, Import Multiple Excel Tables, Power Query Function Table.UnpivotOtherColumns, M Code Table.UnpivotOtherColumns, Table.PromoteHeaders Power Query Functions

Id: bJ0tr50BzhA

Length: 23min 59sec (1439 seconds)

Published: Mon Oct 31 2016