 Hello everyone and welcome back to another video. In today's video, we're going to be looking at a particular scenario and how we can use Power Query to give us our desired result. So we have a very simple data set consisting of eight rows and you can see for each row we've got a person's name and each person has a number of codes associated to them. So for person number one we can see that this cell contains three different codes each separated by a hyphen and as we look down each person we can see they've all got different codes and some have got more than others so majority have got three codes but we can see person number six has actually got eight codes in there so you know we know that this is varying in size as well. What we want to do is rather than have this view or the only available to us we want to split these over three rows so taking person one as an example and let me just do it so you can see visually on the screen this is the desired result that I would like to see for each person. So I'd like to see two columns still but actually rather than person one only being on one row I want the person's name split by however many rows there are applicable to how many codes they have and you know I've just done it quite quickly here for one but let's imagine that rather than just eight rows of data I've got maybe a hundred rows so obviously it's something that would take quite a lot of long time to do manually and that's where Power Query is obviously going to make this a lot quicker and efficient for us. So in order to get started the first thing we're going to do is convert this range to a table. You don't need to do that this stage Power Query is clever enough to do that conversion for you but I'm going to do it just so we can make sure that we give our table a name that we can remember as it become valuable in the next step. So in order to insert a table all we need to go is insert and go table you can see it selected our range because I've just done it and then double click or click this just so it knows that my my table does contain headers and then we're going to go into this or it'll load the table design sheet and I'm going to just rename table 11 and yours might say table one but I've been planning the sheet earlier so that's why it's showing table number 11 for me. I'm going to call this input underscore data and hit enter and we can see that that table is now called input data so we're going to remember that input data. We'll now go to data our data tab being more specific and under the get transformation data I'm going to go into from table slash range and we can see once it's loaded we've got this query that's been created over the left hand side here and we can see that our data set resembles that which we are just looking at. If you're not familiar with Power Query we're not going to go in too much detail in this video but if you are a beginner don't worry there's not going to be anything that you can't follow along and I'll try and cover off some detail where applicable. So we can see if let's step back so looking at Power Query we can see there are kind of three main sections that we're going to be looking at in this video or well four maybe. So the first part is we've got a query's pane so this is going to show us all of our available queries for us it's only going to have the one. The middle section you can see a split in two so we've got our formula bar here so we can see what's going on and also we can then see how our data set is looking and on the far right hand side we've got this query settings pane which contains the properties so that we can rename our query and also the steps that have been applied to that query. So the first thing I'm going to do is rename this so it appears confusing at first because it looks like this is our table but actually this is a new query based on our table. So I'm going to change this and call this output if I can spell output correctly hit enter and you can see it's updated our query's name and if we were to go into the steps so we can see we've got two at the moment so going into the first which is source so this is where our output query is getting its data from and as you can see it's an Excel query and it's looking at our current workbook and it's going to name input data. So as I say not to be confused obviously it's not going to say table name it just says the name of the item is input data so which is obviously that table we created the style and it's going to be pulling in all the content. The next thing what's happening in our steps is it's just going to change the formatting so you can see at the moment it's identified that the name column should be text identified in top left here and the code at the moment is identified as being text as well. If you wanted to change the format of these you could select the top left or select the icon and it will allow you to go whatever format suits you best but for the time being we'll leave it as text and all we're going to do is go over here to close and load just so we can get out of the query editor and also get our data loaded into the sheet. Upon doing that you can see it's created a new sheet for us called our output so let's just move that to the right hand side because I like to work left to right so what we've now got is we've got two tables and two sheets the first one and the blue table so it's nice that it's differentiated the color for us is our source table so we're going to call this our input and this is not connected anything it's just a static table that we have to update and amend accordingly but if we go to our output sheet we can see that this table is now connected to our input table and to demonstrate that I'm just going to go in here and add in another person let's go person number eight and let's give them a number of codes oops just added a line by mistake if it goes output you can see initially nothing's happened if I right click and go refresh it's going to identify and pull through that other value so basically when you refresh the query is going to refresh go to the source and then pull through any additional values alternative if I go to input and we go to delete this row and then refresh this one you can see it's now going to update it and remove that row because it's no longer there so kind of phase one is working you know we've we've got our initial query but we want to update this query so that it's obviously separating our codes based on the hyphen and also separating them onto that applicable their own separate row so I'm just going to just jump into the query by double-quicking clicking over the side here so the first thing we want to do is separate based on that hyphen so all I'm going to do is just right click on our desired column but actually I don't even need to right click just left click and then under the home tab you'll see there's this option for split column so if you click the drop down and go by delimiter and those who are familiar with using texture columns in excel will be used to this obviously the layout is slightly different but what we need to first do is select our delimiter so for us it's going to be the hyphen but it could be any one of these values and generally speaking it's this functionality is really good it'll automatically pick up what the likelihood delimiter is going to be for you but if it hasn't then you just obviously need to select your desired one from here ours is apparently a custom one and you can see it's been added here if you had a different value so maybe you've got a pound sign or something else custom all you need to do is just add it into this box here for excel obviously pick up desired as required so I was going to put our hyphen there next part is you can obviously decide how you're going to split the data you can obviously play around with these but for our purpose we just want to make sure that every occurrence of that hyphen we need to obviously separate our values into separate columns and we don't need to worry about anything else at this stage so let's just click okay and you can see what it's done is it's separated our codes into their own columns what happens when this query runs is it will go through and it will find you know what in essence what it's going to do is go and find okay which row or person to make it easier to follow has the most amount of codes for us we know it's row six so we know or the query knows that it needs a maximum of or a minimum as well of eight columns to separate all those values for person six and you can see that all of them have been populated here for any people or rows that don't have those eight codes like any of the other ones you can see this top one is the first example you can see or separate the first three or the only three and then any other values or columns will then just show this null result to show that it's not applicable to that one there the next thing we now want to do is rather than have obviously these various codes over various columns is we want to split them so that actually they have their own unique row we can really simply do that all we need to do is we could go and select all of these columns but the easiest way i like to do it is just select the name column and right click and then go down to unpivot other columns you'll see when using power query you often have this ability to either unpivot your selection or unpivot everything that anything other than your selection if i've made that sound correct so let's go unpivot other columns and you can see what is done is almost done this transform or transform as in the transform paste that we get to do or transpose not transform sorry transpose and we can now see that our name our field has been broken out via the applicable number of rows to suit the columns i'm not the columns the codes so person number one only had three codes so we can see that they have been split out here so we've got person one and three codes applicable to them and also very cleverly what the transpose is done is is excluded any with null so rather than having person one eight times just because person six does it hasn't done that it's only done it the number of times that it needs and if we go down to person six you can see that they've now got eight rows because they've got codes one all the way through to eight we could leave that there but just be in particular and to tidy things off we don't need this column here the attribute what was previously the column names so all i'm going to do is remove that by right clicking and selecting remove so we're left with our two desired columns and now the real benefit over here of the applied steps is it just gives us the ability to see what's happened and if we need to amend anything along the way we can do that so let's just quickly step through those once again so the first thing what's happening is we're going to pull in data from our input data table the next thing what's going to happen is it just make sure the format is correct we're then going to go in and split out by our via our delimiter what it's done here the next thing is it's also changed the type so you can see it's just made all of them now integers rather than text you could obviously change this back to text if you desired by updating in here but we're going to overstep that for them or step past that at the moment we're then going to go into unpivot so basically it's going to make sure that everything is in rows rather than columns and lastly remove that attribute column just because we don't need it so we're basically left with what we want so let's now go close the load you'll see it's now going to refresh and then what should happen is we end up with our desired result yeah so we've got it there so let's just get rid of this to tidy things up so yeah we can now see we've got all of our codes split out and the brilliant part of this now is as more people are added so let's go person eight again and let's say that they've got four different codes and I'm not going to do the same number here because I'm just being lazy like that we go into our output section refresh this and you can see the person number eight has also now been pulled through as well so we've got result and we've achieved what we want the only thing to note here and it's important as you're doing this is because our query was built based on our current data it's only going to give us the ability to split out by eight so let me just make that a bit more simple to understand if we go back into power query and we go into our step which does the splitting you can see that the formula for doing this is hard coded so what it does is it says right we want to get our codes column and we want to split based on obviously this hyphen here and we want to split into eight columns so this is where I said obviously for those they don't have eight codes it's going to show null in the empty ones but it's obviously fixed to this total number of eight columns so if you were to go and add into your source table or input table should I say and wait for it to load so let's say we go into here and we do person number nine number nine and maybe they've got so I need to do nine in here three four five six seven eight nine let's just do ten to be safe and we don't want the additional row there because this one has got more than those eight columns what will happen when we refresh this is they won't all be picked up so you can see it's only going to go to a maximum of eight rows or eight columns obviously as in the steps so this is just one thing that you need to bear in mind if you are trying to build this or so that it can be reusable so that as more information is added to it or you know you can use it on an ongoing basis one thing you want to factor in is to make sure that you know you have your most number whatever scenario of code you have so for this one if you need to factor in that if there's a potential to have 20 codes here that you need to have built that into your query when you've done it but to be fair because it's such a quick solution you could just do this every time that you need it and that way it would update accordingly but that's just one of the things to note in there is whatever the maximum number of codes is when at the time of building this is the maximum you'll ever have available so any additional codes won't be picked up I hope that hasn't gone and confused you but yeah it just wanted to make sure that you were aware of that but nonetheless I hope you enjoyed that video if you haven't subscribed to the channel or maybe this is your first time finding the channel please do subscribe and make sure you hit that bell notification button that way as soon as new videos are released on the channel you will be notified of them and if you did like the video and it was useful to you please don't forget to give the video a like it's not only greatly appreciated by me but it does help the all-important YouTube algorithm for our channel once again thank you very much for watching and we'll see you in the next video