Power Query Unpivot - fix 4 common data layouts (incl. workbook)

hi I'm member Tracy from my online training hub and in this tutorial we're going to look at Power crews on pivot and pivot tools one of the most common data layout problems we exhale users encounter is data that is already in a pivoted or partially pivoted layout and these formats are bad because we can't use pivot tables or formulas the way they were designed and so we end up a ghen izing over ridiculously complicated formulas in an attempt to work with that poor layout but now that we've got power query we don't have to put up with poorly laid out data anymore because it's really easy to fix so I'm going to show you how we can use power crews on pivot and pivot tools to convert some common data layout into the correct tabular format so you can work with Excel pivot tables and formulas the way they were intended you'll be power query on pivot masters by the end by the way don't worry about taking notes because I've provided step-by-step instructions in my file which you can download so the first data we're going to look at is this table of salesperson data and we can see we've got it partially pivoted because we've got a separate column for each year and we've also got this grand total information now this is our source data so we shouldn't really have any totals in it at all and we really need a single column for the year and then a single column for the amount this is formatted in an Excel table so it's really easy for power query to grab it now I'm in Excel 2016 so I'm going to go to the data tab which is where my power query tools are in the get and transform group if you're using Excel 2010 or 2013 then you'll have a separate tab for power query and you'll be able to load your data into power crew from there I'm going to go from table or range that's because my data is in a table so it will click that power query is going to grab the data and it opens the power create editor window now let me just drag it the cross interview okay so this is my power when though you can see it's separate to the Excel window and while this is open I can't actually click into Excel so we're working in power query and the first thing I'm going to do is get rid of the column that I don't need the grand total column so I'm just going to click on the column to select it and press Delete looks very much like Excel we've got a ribbon and a grid and this is our data that we can see from our table now as I work with the data that I see here it's not affecting the source data it's taken a copy of the data for me and I'm just altering that copy so now what we want to do is unpack these columns we can do that by selecting them and then on the transform tab if I make the screen a bit wider it might show me the actual name so unhip it is up here if you have a bigger screen then you'll have on pivot beside this icon I need to click on it to reveal my option so I can unpin up the selected columns or I'm pivot other columns or under the only selected columns I tend to always use on pivot columns or under the other columns just future proofs my days so if I were to get another year added to this I can choose a none pivot columns and then when i refresh it will include that other year and my dataset and I'll show you that in a moment so where I'm pivoting the columns with values in that's typically what you want to do when you're on pivot or I'm pivoting the value columns as opposed to columns that contain text so unfit now we've got a column for our year so we can rename that just double click on the column header to rename and we'll call this sales so now Isaiah is in the tabular format I'm just going to change the type of this column so at the moment it thinks it's text but it's actually a whole number because that's our year it will change the data type and you'll notice over here power career has recorded the steps that I've gone through to get to this view of the data and now I'm ready I can close and load it so I'm going to give this query a name that clean sales data now I can close unload and that will put it to my default load location in which in my case is just a table in Excel or I can choose close and low to and I get a few more options let's have a look at those so let's bring the dialog box up into view a bit better so these are my options I've got loaded into a table go straight into a pivot table report or a pivot chart or only create a connection and there are some occasions where that's really useful I can put it in an existing worksheet or I can add it to the data model I'm going to put it in an existing worksheet and I'm just going to select a cell beside this table actually I'll give it a bit of space so pop it in there and I'll click OK and pal queries going to load the data in and you can see the query pane opens up over here and I can see this is my query and this is my separate set of data that's linked to this source data this is in a proper tabular form outside to now go ahead and create pivot tables and reference it in formulas but what happens when we finally get the new date of 2016 let's just put some dummy data in here I'll just add a value to that now if I want to update this table all I need to do is right click and refresh and now my 2016 data is also included I haven't had to go through all those steps again it just updates for me on a simple refresh and anything that I have linked to this table will also be updated so that's a fairly straightforward on pivot let's have a look at a more complicated one I'll close the query pane so in this case I have data that's sort of partially tabular this part really in the right format but then I have these columns across here and it's all gone a bit pear-shaped so I've got actual hours for Jan and Feb and then actual costs for Jan and Feb and really what I need is a column for the month name a column for the hours and a column for the costs again this is formatted in an Excel table so it's nice and easy for power kori to get it so in data from table or range and if you have Excel 2010 or 2013 and it would be you would go to the power query tab as opposed to the data tab so I've got the preview of my data and I need to unpin it again the values column so I'm going to select the value of columns then transform an pivot so now I have a column for my attribute and a column for my values but the problem I have is I've got hours and costs in the same column and I really need them separate but I also need to separate the month from the type of cost or hours the attribute itself so if I select this column let's split the month out so we can use the again on the transform tab we can split the column now we can choose from delimiter or number of characters I'm going to go with number of characters because my month labels are all just three characters long and I'm going to do it split at once as far left as possible and I'll click OK now we have a space preceding this attribute field so I need to clean that let's go and trim the space out and now I have my values I still need to get them into their own columns so this is where we can actually pivot the data so we can pivot the attribute column pivot where are the values it's asking me where the values are in the column called values in this particular case in my last options I can change the aggregation type sum is fine for this purpose and I'll click OK so now I have a column for my month let's rename that a column for my hours and a column for my costs and now I have the perfect tabular format let's go and close and load it to and I'll put it on an existing worksheet I'm just going to pop it underneath this data now when you're doing this don't ever put it on the same sheet because eventually you're likely to come into a flash where this table tries to right that table but we can see here for the purpose which is learning the correct format our data is now in and again we haven't changed the source which has created a new view of the data okay let's look at a different scenario this is a really common problem probably one of the worst things we Excel users can do and that is where we put header rows over two rows when really we should only ever have headers in one row so we need to merge these two rows together and then we need to unpack these so that they're in one column for ours and one column for cost and you'll notice this data is almost exactly the same as this data in here it's just we've got these nested header rows the other thing to notice here is this data is an inner table yet so let's format it in a table control here's the shortcut now in this case I need to deselect my table has headers because the headers aren't in the right place they aren't in one row so I can't use either of those rows of headers I'm going to click OK Excel goes and puts in a new default the header row for me and then I have my actual headers in the first two rows of the table so let's go ahead and load this into pal query from a table now in order to get the headers into one row we need to transpose the data because in power query we can merge two columns but we can't merge two rows so let's go ahead and transpose the data on the transform tab transpose so now we have our header rows in column one and column two so we need to join them together but before we do that we need to copy down January into the row below and February into the row below as well so we'll select column one and we'll fill down now I've got Janet said on each row that they relate to we can select column one and two we just hold down shift to select two columns just like it in Excel and then upon the transform tab we're going to merge columns it'll ask us if we want to put a separator in and it's a good idea at this point to choose a separator that's not present in any of your data because if you want to separate this information out again later then you'll want to have something unique now I'm actually going to break that rule and choose space because as you'll notice before when we split the column we chose to split by a set number of characters and I'm going to do the same again because Jan and Feb are three characters so it doesn't really matter if my separator is repeated in the data the new column name I'm going to leave it at merged it doesn't really matter because I'm not going to keep it so click OK so now we have our column headers in one column at the moment we need to transpose it back but before I do that I'm just going to trim that extra space out of the front there so we're going to format and trim now we can transpose and now we have all of our headers in one row we can actually promote that to the header row now use first row as headers now we've got our headers in the first row and you'll notice our data is back to the previous example where we have separate columns for hours of January and hours for February and actual for January and actual for February so we need to unpick the data you'll know not see now that I'm pivot columns icon has enough room to display completely so I'm pivot columns let's split the column by number of characters that's three for the month and once as far left as possible we'll trim the space that's at the front there and then we need to pivot the attribute where are the values in the column called values and we'll leave the Advanced Options is aggregating it some and that's fine we'll rename this and they're good to go so we'll just close unload - let's pop it below this table so you can see them together and we'll click OK and there's our data or corrected in a tabular format ready to use so we've covered three of the main types of pivoted data that we need power query to unpin it for us let's look at the next type and you'll notice over here we've got my queries I can go back into the minute at them at any time so in this example I've got a load of data that's repeating or stacked if you like so I've got entitlements name date of birth Ally rate holiday leave hours and holiday leave accrual then I've got a blank row and a total and then a blank row and then the next set of data and that repeats exactly the same pattern all the way down and it's no good to us in this format we can't use formulas easily with this type of data so first thing I'm going to do is format it in an Excel table because that makes it easy for power query to consume it so ctrl T it has headers there in one row and they're fine we'll use those let's go up to the data tab from table now the first thing I want to do is get rid of the blank rows and the totals remember our source data shouldn't have totals in it that's what we use Excel to figure out so we'll get rid of the nulls and we'll get rid of the totals and click OK so now our data is all repeating that's fine the next thing we need to do is pivot the data the problem that we're going to have though is some of the values repeat or they're not all unique for example some people are on the same hourly rate and they're probably some people at the same data birth so we need to add a column that contains a new unique value we can do that on the add column tab I'll just add an index column we can choose from to add an image from 0 1 or even a custom number I don't really care for this purpose it just needs a unique number so now that we have our index we can pivot the entitlements column transform tab and then pivot column it's going to ask me where my values are there in the column called value and then in the Advanced Options here I don't want to count them I want them to remain as they are so I need don't aggregate and I'll click OK so now I have my name date of birth Ally rate holiday leave hours and accrual all in their own columns I have a bit of a problem though because the data is sort of stepping down onto the rows below so each person has the data scattered over five different rows so what we need to do is use our fill tool to copy the data up so we need the data but for Brenda's copy up or fill up to the row above we need her hourly rate to fill up so that the row is the name has the data for each person on it let's do that with them selected I can fill up and now if we look at Brenda her date of birth Ally rate leave hours and holiday leave accrual are all on the row with her name and likewise is Brian his date of birth hourly rate holiday leave hours and accrual you'll notice Brian's data is encroaching up into Brenda's sort of area but that doesn't matter because I'm going to get rid of all the null rows so that all we're left with other rows with the names and now I don't need my index column so I'll select it and press the Delete key and our data is ready to go I might format those date of birth field so that it's just a date I'm pretty sure I don't need the time everyone was born at and if you look back on this go back to the previous step everyone is born at 12 a.m. according to power careers just the default power query assumes the time if there isn't a time in the value and by changing the type to date it just removes the time element okay so we're ready to close and lo let's load it to the same worksheet so we can see at the side and we'll pop it in there and they'll click OK or by the way you can add the data direct to the data model so that's power pivot and in that case you'd only create a connection and then you pop it straight in the data model so that your data isn't repeated multiple times in your workbook so anyway we'll pop it in the table in a table beside and we can see all of our data in a nice tabular format ready to work with and if anything changed in here or I added more rows to my table then I would just refresh the query and this would update and anything I had referencing this data would also pick up those changes so there are quite a few scenarios on how to get data that pivoted and unpin it and they're some of the most common scenarios you're going to come across so hopefully with these examples you can tackle almost anything and don't forget this is the workbook that you can download and you can see there is step-by-step instructions with screenshots on how to do everything same for each example that we just run through so go ahead and download that workbook and become untethered masters in power query ok thanks for your time

Channel: MyOnlineTrainingHub

Views: 107,905

Rating: 4.9853587 out of 5

Keywords: power query unpivot, unpivot, excel unpivot, excel power query unpivot, unpivot power query

Id: -IMqkg35adA

Length: 19min 24sec (1164 seconds)

Published: Mon Jun 05 2017