Convert Multiple Column Groups to Rows in Power Query

in today's video we are discussing the solution to the power query problem that I gave you in the last video which is where you had to kind of take the multiple column groups and convert them into rows if you tried but if you weren't able to get the solution that's totally okay we have a lot of incredible Solutions and one solution for me let's just get started with this one okay let's just quickly recap the problem once again and then just go take a look at the solution we had simple data we had two columns by the way the First Column was the names of the cities and underneath every single City we had not one but two columns name and the H now I need to convert this data into a more tabular layout something like this which is where the first column needs to be the city column then we need to have the name column and then we need to have the H column on converted into a tabular data now this solution needs to be dynamic that means tomorrow if any of these columns within each City or the cities itself expand and you have more columns added your Solutions should still work now one of the ways to solve this problem is that you first transpose the data then maybe you unpivot the data and then you pivot it back again and this is pretty much going to solve the problem now what happens is that especially when you use the pivot or the transpose operations in power query the streaming of the query breaks now I may just do another video to explain to you what does streaming mean but in simple words streaming simply means that when you're forcing the query to go beyond the preview of 1000 rows and read the data beyond that which slows down your query at times we'll take a look at how do we build a solution which is where neither do we have to do a pivot nor do we have to do a transpose and we'll be able to get to the answer let's just have some fun with the m code together all right fellas in Excel power query here I have loaded the data before I start to build the solution and write some M code together I would want to build some formative understanding of how and what are we going to do so that you're able to visualize what the solution is going to look like so essentially what I'm trying to do here is that I'm trying to extract pairs of two columns since every single city which is let's say Mumbai here has got pairs of two column name and age the next city has also got pairs of two columns I'm trying to extract pairs of two column and stack them one underneath the other something like this so take this name and age and just keep it right here take the second pair of the column which is name and age and keep it right here take the third pair of name and age and keep it right here so on and so forth and once we have stacked the pairs of the two columns one underneath the other then I will find a way to get all the cities as the third column of the data somewhere here all right enough talking let's just actually put some M code to work and start to build the solution as the first step to solve this problem I'm going to extract the names of the columns which are currently stuck in the second row of the data so here is a table in this table you can see that the first main header is the city and the sub header is the name and the age which are nothing but the names of the columns I would want to extract these two columns how do I do that I'm going to go in this entire table in this entire table I know that the data is there in row number two and I will just extract the data from there now for power query this is row number two sorry and you can see that this seems to be row number two but for power query this is actually row number one because the counting starts with 0 in power query so I'm gonna go to row number one get this entire record right here and then do something with the record how do I do that I'm going to create a new Step this refers back to the table which is the table in front of you I'm going to say from the table you can see right here please give me the first row of data once I get the first row of the data a row of the data is nothing but a record and you get a record right here I need to convert that record got into a list so that I can use it later I'm gonna say hey this is a record why don't you convert the record into a list using the function called record.to list I commit I do get a list but the columns have been duplicated let me just remove the duplicate so I'm just going to use the function called list dot distinct and I will get rid of any duplicates in the list in front of me now I just have the unique names right here so names of the columns we have name and age should the query expand and you get another column in here that is going to automatically be added in right here let's move to the next step once we have been able to extract the names of the columns in the step right here the next thing that I'm going to do is convert all of the columns that I have in a form of a list and I'll explain that to you in just a bit that why are we converting that to a list but remember that we had to convert all the columns into pairs of two columns and that's exactly what we attach to so essentially what I'm trying to do here is that please get rid of the first two junk rows of the data that's part one once you do get rate of the first two junk rows of the data then why don't you convert this into a list list number one this also into a list list number two list number three list number four list number five so on and so forth so all the columns converted into a list of their own how do we do such thing I'm gonna maybe come right here and create a new Step but I'm not trying to refer to the columns step right here I'm trying to refer to the table which contains the entire column so I'm just going to say hey this is going to be sheet1 and in the sheet 1 I would want to get rid of the first two rows of the data so I'm going to use a function called table dot skip and I'm gonna say hey this is a table from this table why don't you just get right off the first two rows I do that and I land up with only the columnar data now once I have been able to get this I will convert all of these columns into a list now this becomes a list and then this becomes a list and then this becomes a list so on and so forth how do you do that there's a function available in power query called table dot two columns and all the individual columns that are there in the table as of now will be transformed into a list of its own please take a look I convert the bracket now sorry I close the bracket and I commit to the formula press enter and I do get a list so the First Column is a list the second column is the list the third column is a list so on and so forth as of now the problem with the list is that as of now in the list I do have the null values and I do not really want to have the null values I can get rid of the null values by using another simple function like a wrapper function around it I can say that hey within each of these lists why don't you just get rid of the null value so I can use something like a list dot transform and I can say hey I'm just trying to work with this list in this list there are lists inside of the list and take a look at every single list and just get rid of the null value so I'm going to say something like each list dot remove nulls and I'm just gonna maybe put the underscore right here to refer to every single list and close the bracket and press enter this still gives me the same output but the difference between the previous output and this output is that the nulls are gone all right this is where things get really interesting if you understand this probably you would have understood everything that I have done so far what am I trying to do if you take a look at so far what we have created we have been able to create all the columns into a list that means the first name column is in a list the second age column is in a list the third name column is again into a list and so on and so forth but I'm trying to create pairs of two because if you remember that here this was the first pair this was the second pair this was the third pair and I'm just trying to bring the pairs underneath one another so essentially what I'm trying to do is if you take a look at the list right here this is my name the second list happens to be my age and I would like to bring that right here this is one pair then I would like to pick up the next two list pair them together and create uh like a table the First Column and the second column is like another pair and then another pair and then another pair that's what I'm trying to do how do I do that I need to create some sort of pagination here which is where the list automatically breaks after every tool list so that means these two lists are somehow packed together and the next two lists are packed together so on and so forth how do you do such a thing I'm going to use a very interesting function in power query called like a list dot split and with that function what I can do is I can split the list into pages of two so I can say hey here are here are multiple lists that I have I wanted to split them into pages of two now I as soon as I close the bracket and I press enter eight lists are now converted into four lists if you peek into the list you're going to have two sub lists this is going to be the name list and this is going to be the age list and again this is going to be the name list and the age list and so on and so forth so every list has got two sub lists which contain the two columns now as of now these two are again lists it's not really like a table where you have both the columns back together so what I would want to do is I would want to convert this little list right here which is like packed together into a table format so that I have the two columns together how do I do that I'm going to go in right here and I'm going to say something like list dot transform and I'm gonna say hey uh here is the list that I'm trying to work with every single list has got two sub-lists why don't you take those sub lists and convert those into a table all right so I'm gonna say something like each um table Dot from columns which is the inverse of the table dot two columns function if table dot two columns function converts The Columns into a lists the table Dot from column function converts the lists back into a table so I'm going to say table Dot from columns and I'm going to say something like an underscore right here let's just see what happens close the bracket and press enter I do get a table if I pick it to the table you can see that the pairs of the two lists have been converted into a table format but as of now these have poor headers so I don't really want column one and column two I actually want the valid headers which is the name and the age did we capture the headers in it any time before sure enough we did and here is the step which is where we actually did capture the headers now what I can do is come back to this function and I can say an optional input to this function is that do you want standard default headers or do you want custom headers so I can say that hey I already have the columns which is nothing but the headers that I did capture which is what I want to be stuck at right here I close the bracket and I press enter the output Remains the Same but the difference is that it actually gives me the right headers for each and every table before we move forward with the next step there is a small Nuance that you have to keep in mind as of now if you take a look at the function that we have written we created pages of the lists and we hard coded the number two this means that the lists are always going to be split into pairs of two columns what if you have extra number of columns in the data let me help you understand if you go back and take a look at the data as of now we did have only two columns as a pair of the columns within each City but what if you have three columns right here so you have name age and maybe a weight column added here then the list should actually be split into pages of three but not pages of two how do we accommodate for that if you now take a look at the number of columns right here this is definitely going to get updated so if you have three columns this should also now show three columns in here so rather than writing a manual two in here I'm gonna say that please do not write a two why don't you count how many columns do I have here and please make a page of every single count of that column so I'm gonna say don't write a two I'm gonna say list dot count and count what count the columns query right here and just keep that right here and now this is truly Dynamic now our solution has been built but the problem in the solution is that as of now the four tables that we have right here I can certainly combine the data of the two tables so the first table has got name and age I can make the other table stick right here append it the third table is going to be appended underneath that and the fourth table is going to be appended underneath that good enough but where are the locations so we need to do some work to be able to capture the locations as well if you just quickly take a look at the locations our locations were stuck in the first row of the data and I'm going to go ahead and peek into the first row not the first row actually the zero throw of the data and take the locations out from there so I'm just going to create a new step in right here and I'm going to say that I don't really want to refer to the Customs tab I want to refer to the navigation step the name of that step is sheet1 from that step which is the entire table I would like to take a look at this zeroth rule of the data which is where all the locations are kept I get a record I don't really want a record I would want to convert that record into a list so I can say something like a record dot to list something that we used before close that bracket press enter the problem is that as of now this does have null values and I do not really want to have that and I can say list dot remove nulls and I can commit and close the bracket and this is going to give me the unique locations that I have all right almost about to end the query here we have almost got everything that we need we just need to pack everything together please take a look so here we have all the locations with us but this is a list and in the previous step we also have all the tables with us these tables are now also packed within the list although you have tables as individual items but they are actually packed within the list what we need to do is something like this I need to create like a two columnar table the First Column is going to be all the locations so location one location two location three and location four that's going to be the first column of the table the second column of the table is going to be table one table two table three and table four what tables these tables that you can see it right here and once we have been able to create like a structure like this I can expand all of these tables together and I'm going to be done with my question right here how do we do such a thing what I need to do is I need to take two lists this is list number one uh right here and the other list is going to be all the locations right here and I need to form a table by two lists and one table how do I do that I'm gonna write a very simple function that we have used in the past as well just a while ago which is table Dot from columns and I'm going to say hey I have two lists the first list is going to be my location so that's going to be my first list and the second list is going to be my custom one so that's my custom one and that needs to be converted into a single table so if I now convert if I now take a look at the output of the function I have been able to get the locations from here I have been able to get the tables from here pack them together and form a single table out of it now if I take a look at this table this is pretty good all I would want to do is I just want to maybe call this column as locations column I do not really want to add a step later so I can just maybe go in here and I can say I would like to add two columns in here the First Column is going to be let's say location and the second column is going to be let's say column 2 or C2 doesn't really matter I'm just gonna maybe put this in the curly brackets because this accepts as a list press enter and this is the location column this is the C2 column now if you do expand the table right here all of this is going to belong to Mumbai New York Hong Kong and London let's just expand this if I click on the expand button right here and click on ok now the problem with the expansion is that that the expansion has happened but the names of the columns are again hardcore it which is again going to cause a problem in in case my query updates so what I need to do is see here is that I will say that instead of writing the names of the columns manually right here I rather would want to refer these column names from the step that I just created a while ago so I can just get rid of all of that and I can say that I already have the names of the columns and please use that and that is our output without using any sort of like transpose pivot or unpivot of the data all right that was quite a long and tricky one and I'm sure that you can solve this very query in a more simpler way in case you're working with smaller data but in case you are trying to figure out the solution for a larger query which is where you would want the streaming not to break and you are trying not to use any sort of pivoting uh unpivoting or transposing of the data you can consider are parts of the solution to implement in your own problem solving process I hope you like this one in case you have any questions around this please feel free to drop in a comment and a big big round of applause to everybody who participated thank you so much for taking out the time and posting your answers in the blog comments there are some incredible Solutions in there in case you're watching this please do take a look at all the solutions as blog comments as well in the end I'd like to give a big shout out about my tax and my power query courses in case you are starting out with power bi and learning power query tax and data modeling is hard and you would like to master the fundamentals first and then graduate to solve more complicated sophisticated problems even of your own data I'd highly recommend that you take a look at my courses it's going to be super awesome thanks so much for sticking all around all this while in case this was a very very complicated one let me know if you have any questions and I will see you guys in the next video cheers [Music] thank you

Channel: Goodly

Views: 46,833

Keywords: Power BI, Power Query, Excel, DAX, M Formulas

Id: e0Rn8HRr-0M

Length: 17min 18sec (1038 seconds)

Published: Thu Sep 29 2022