How and why to Unpivot data with Power Query

what is unpivoting well it's critical to getting your data in the right shape and it solves so many problems in excel and power bi when it comes to reporting i've got a simple version that i'll do in excel and a more complicated version that i'll do in power bi let's go so here's my data for my basic example this sort of data is really screaming out to be unpivoted it's got the same thing i.e shop name in different columns if you try to do a pivot table off this let me just show you quickly summarize with pivot table okay and you try and start doing you know you want your total sales by product well you can't you have to drag in shop a shop b you know you can't do a total and you can't slice and dice by shop name or by you know any other category so pretty horrible so what do we do well we use power query to flip those three columns into more rows three times as many rows so we're inside this table i've already turned it into a table using ctrl t i give this table a name under table design i've called it tbl basic scenario up in the top left here so that's the starting point and then i right click and say get data from table or range or click in here and say data from table range or from sheet if that's what it says in your version so we pull this data in to power query and we're now ready to do some playing about now i recommend if you're going to flip your data around just get rid of this change type step so that you're not hard coding in shop a or shop b okay there's no need to so don't do it so get rid of that change type step and then control click on the columns that are already nice columns i.e date and products and it's the other three we need to flip around so we right click and say unpivot other columns this is a glorious thing we've now got three times as many rows okay so we haven't lost any data we've just made the table longer rather than three columns wider and then we'll simply call this shop and we'll call this units and we'll just go ctrl a transform detect data type date time i'd rather that be a date replace text text whole numbers great okay what do we do with this well we should give it a different name so i'm just going to call it basic scenario data for example and then home close and load close and load two and you could load it to a table if you just wanted the table flipped around or we could load it as a connection to the data model if we were going to do a pivot table so we can say ok and this then loads to the data model and all we now have to do is insert a pivot table so i'll just go over here a few cells to the right insert pivot table using this works books data model or newer versions of excel you click on the drop down and there should be an option from data model okay so using this workbook's data model and now here's the basic scenario data and i can now put units in my values and look shops just goes in my rows or shop goes in my columns depending on which way i want to filter it or products goes in here and then right click add shops as a slicer it just gives you way more flexibility because i unpivoted the data okay that is perfect so that's unpivoting but it's not always as straightforward as that so let's look at a more complicated example so with this table we have some audits being done by these people on these shops on these dates and we've got a column showing the number of review points and then there's certain criteria that have to be met so yes they've passed those criteria for that shop or know that or it's not applicable okay so these three criteria are crying out to be unpivoted they're the same thing their criteria but if we unpivot this and make the table three times longer then this 20 will get repeated three times and this 10 will get repeated three times so we'll end up with rather than 85 review points we'll end up with three times as many and your dax then has to get more complicated to handle this and none of us like complicated dax so this would be one way of approaching this and i'm going to go for a very sort of simplistic approach um let's do this in power bi so i've got a power bi file um this is my template file that i start my projects with it's already got a measures table in and a calendar if you're not too clear on the calendar table a little link will pop up and i'll put a link in the show notes as well the calendar is in there for hooking up and doing analysis by day i've got relative periods and things like this so go check that out right let's go and get that excel data so i'm just going to go and close the excel file down i'm going to go excel workbook there's the unpivot file and it's scenario 2 right click transform data here we have scenario two let me just show you what happens i'm just gonna minimize this little window a second if i go across and highlight these four columns they're all nice columns and then here's the criteria and i right click and pivot other columns see my numbers get repeated and i just can't simply add those up to give me total number of review points anymore and my dax might need to be an average or some sort of special you know dax in there to handle that that's not great so this is really a bit of a data modeling challenge so what i'm going to do is start by just renaming this one okay and this is going to be my master source and then i don't want this to get loaded into the model i just want it to be a like a starting point so right click and then disable the load by clicking on that little icon right i now want to split this table apart into two because really it's two separate sets of data we've got our sort of number of review points and then we've got our criteria okay we need to have two separate tables with common fields the common fields being and again you can you know go to whatever length you want to go to with this but date so i can hook it up to my calendar i'll actually create an auditor table and a shop table those will become my dimension tables and then they'll link down to the other two tables underneath okay so let's create your auditor table first so i've already got the calendar that's my date table so right click reference so i just want to pull from that query right click on auditor remove the other columns and right click remove duplicates now in real life using people's names as keys and tables is not great ideally you have some sort of employee id or code much more secure because typos and stuff can cause this to go horribly wrong um an even more advanced way of doing this is to add an index column now to this table and merge it back onto the side of your master source potentially or a feeder table off this the actual next table i'll create and then you just have the index the auditor id the order to index in your fact table which will potentially make your model smaller and faster however we're sticking with the basics here okay so here's the ordered table i've removed the duplicates and let's call this auditor table right do the same thing for the shops so right click reference right click on the shop remove other columns right click remove duplicates and again if you are going to use text then maybe back in your master source make sure you uppercase these or use the trim and use the trim function or the clean so right click transform uppercase trim all those sorts of things especially if your data is being typed in they're all the sorts of things that cause these keys to break okay so this one is going to be called the shop table right click on your master source and i want to split this one apart so again reference and those four columns the first four are going to be my review data so right click remove other columns and this will be my review data back to master source right click reference and then these four columns again control click on those right click remove other columns i could have just clicked on the other column and removed that one i guess and this is my criteria data and it's this one that i'm going to unpivot so i'm in here i've got those selected right click unpivot other columns it's a beautiful thing so now i've got my number of criteria and this will be called criteria and this will be called outcome okay that's all good and i'm pretty much ready to go one thing i'll recommend is you know select these right click move to group new group and call it you know audit data or something like that just to show that they're all related to that one you know master source query okay we go close and apply i've probably forgotten something but we can always go back in and fix it i go into my model view so here's my calendar and normally what happens is over the right here these other tables get hidden so let me zoom out so i'll autofit all i'll just drag these across these three i know they're small but i'll zoom back in in a second okay so we have our review data and we have our three fact tables okay so we just expand this out so here we go we're going to go date up to date and date up to date and then we go auditor to auditor same thing here and then shop up to shop and the related fields are pinned to the top so then we can just collapse this calendar stack these three tables on top of each other and we've got a nice sort of layout here so with this it's now easy to do our reporting okay we can simply go back to our report okay so now we can go to our measure and we can say number of review points equals the sum and there's a column called there's a column called number of review points never have a measure and a column with the same name so i'll fix it up now so that works and i'll just go into here and rename this column as column okay so my number of review points i can now slice and dice in here so that's the total number and i can do it by um you know by auditor or rather than by auditor i could do it by shop and then i can slice and dice it by date so it all sort of feeds in but then i can also do my criteria calculation so over here the criteria data i can simply do a count rows now it's a much simpler dax function because i've got my data in the right shape so every row is a different you know record different criteria being assessed so i can simply go to my measures new measure uh number of criteria equals count rows from my criteria table there you go my criteria data that is awesome so i can simply drag that on put it as a matrix visual go for the grid and make it a little bit bigger all right and i can now slice and dice that by shop from my shop table there we go and from my criteria table i can now put the criteria potentially in the columns excellent and i could slice and dice that by outcome so i can put the outcome in here as a slicer make the items a bit bigger and now when i slice and dice these you can see the different criteria i can also click on the shop and it'll filter the data above so it's interactive you aren't doubling up your total counts over here your number of review points that's all good and you can slice and dice it by auditor by shop awesome so i hope you find that useful let me know what you think do you handle this in different ways have you considered what happens when you unpivot are you brand new to unpivoting share this video let people know about the channel and i'll catch you later [Music] you

Channel: Access Analytic

Views: 904

Keywords:

Id: ESap6ptV8fI

Length: 16min 40sec (1000 seconds)

Published: Sat Dec 18 2021