Expand Multiple Columns to Rows in Power Query - Solution

hello people welcome back to the channel in today's video i'm gonna talk about the solution to the power query problem that i gave you in the last video a lot of people replied with the answers on the blog comments and in particular i'm going to talk about two different solutions one is going to be a generic solution and the other one is going to be an advanced solution and i will also talk about that why is the advanced solution far better as compared to the genetic solution and you're also going to take a look at some really sophisticated m code in this video all right no further ado let us start all right people i'm in this power query window i've already loaded the data i'm going to take one quick minute to explain you the problem once again in case you've forgotten about that so let's take a look at the problem and then jump over to the generic solution first so here we have different cost items right here and we have the amount that was spent on the particular cost item and what i have to do is allocate that particular cost item within all of these locations all of these businesses and all of these departments equally now for instance if branding expense was incurred to the tune of 1.25 million that 1.25 million is going to be allocated between us and uk and u.s and uk has got multiple businesses heavy equipment and manufacturing and sales so you have two locations and two different businesses and one different sales department that makes it about four rows of data and i'm going to allocate that within the four rows of data and i have to make four rows of data and in those four rows of data i'm going to have locations businesses and departments all right that was the problem in case you don't really know about the problem i will recommend that you take a look at the past video understand the problem and then come back and check out this particular solution all right let's just get started with the generic solution first all right people i'm calling this particular solution generic because this is not really scalable in case i get another column in the future in which i have to further expand the number of items i won't be able to do that easily because i'll have to then edit every single step that i have done and go make the change in every single step but for now the solution works just fine and we're going to take a look at what the solution is and later we'll talk about the advanced solution as well so take a look um once the data has been loaded what i generally have to do is that i have to split out every single value which is separated by a comma within these three columns so location is currently all in a single cell i have to split this by a comma which is right here and make four rows of this particular data right so and again uh two rows for businesses and you know maybe three rows for department that's what i have to do so what i'm going to do is i'm actually going to create this particular function which is table dot transform columns and i select every single column so location column then the business column and then the department column and what i use is a simple text dot split function to split out the values and convert that into a list now the list is expandable but if i just quickly peek into the list i'm gonna see that the value that was concatenated in a single cell separated by a comma has now come into multiple rows which i can later absolutely expand but for now this is good enough now once i have been able to exp like convert all of these location business and department into a list what i want to find out that this particular expense item needs to be allocated in how many rows of data and the rows of the data is nothing but the multiplication of the number of rows in the locations number of rows in the business and number of rows in the department because the allocation expense has to be equally allocated so what do i do i create another column like a custom column on the far right and let's just explore the formula for that so essentially what i'm trying to do is i'm trying to find out that how many items are there in the location list multiplied by how many items are there in the business list multiply by how many items are there in the department list and you can see that we have used the list dot count function we multiply all the three item values of those lists and divide that by the value and this actually is going to give me that what is going to be the equal allocation amount right i close that click on ok and that's the amount that i actually get pretty good now that we have got the amount the next thing to do which is the simple thing to do obviously i'm sure you can also understand that is to just expand the location column the business column and the department column and this is going to be all good so the next three steps are expand location expand business and expand the department and this amount has been allocated that's the data pretty much that's the data that i actually wanted to see good now this is an okay okay solution or a generic solution because if you take a look at the advanced editor on this particular uh query you're going to see that in almost every single step i have referred to the column name manually so location business department manually hard coded again location business department hard coded location business department hard coded this is going to be like a bottleneck in case i want to add another column i almost have to edit every single step of the query manually to be able to add the additional column and the query becomes non-scalable right but for now it absolutely works fine all right now let's just go take a look at the sophisticated way of solving this problem and in case we want to expand this to further columns being added to the data how can we solve this in a more advanced way let's just write some beautiful m code together all right people you ready for this now for me to be able to build this solution i'm exactly going to follow the same approach that we did it in the past video but the only thing that i'm trying to do this time is not hardcode the names of the columns to be able to successfully do that i have collected all the names of the columns as a separate query in the form of a list let me help you understand what am i trying to say now i know that the cost item and the value item are the two columns in which i see the cost particular and i see the amount right here in the value item all of these other columns are the columns in which the cost has to be allocated so i'm going to dynamically extract all the other columns leaving aside the cost item and leaving aside the value and the names of these columns i'm going to extract it as a list and that's exactly is the query that i have created right here which is allocation columns if i show you the m code for that i'm extracting the names of the columns but the names of the column is going to omit the cost item and is also going to omit the value and here are all the items that in which i have to allocate in case in the future any particular column is added to this particular list i'm going to get the name automatically right here and that's going to be you know sort of an automation all right pretty cool now let's just start to solve this particular query and do it step by step the first thing if you remember what we did in the generic query was that i had to convert every single item of this particular column in a form of a list so that it becomes expandable to be able to do that i use the table dot transform columns function and that's exactly what i'm going to use as of now as well i'm going to write table dot transform form columns and i'm going to start the bracket and table dot transform columns function if you don't know it i have done a very exhaustive video on that i suggest that you take a look at that video and understand how this function works but i'm just going to swift through this so the first part of this function is the name of the table which in which table are you trying to transform the columns so the name of the table is source source is nothing but the step previous step all right and what is the transformation that i'm trying to apply i'm trying to convert every single value of the location column as a list okay so i'm just gonna maybe uh start to write something like location i'm hard coding it as of now but i will automate that in a while and each value of the location needs to be split by a delimiter so i'm going to use the text dot split function and i'm going to say pick up every single value and the delimiter happens to be a comma and a space and i think this is good enough i'm just going to close the bracket and press enter now you can see that because i hardcoded the location column all of the values of the location column have been transformed into a list and if i can just peek into the list i can see all of the values pretty good the problem however is that this is hard coded and i do not really want to do that so what do i do i have the names of all the columns on which i want to apply the same transformation which is text or split the same transformation i need to apply on all of the columns which are stored in this particular list right here so i'm just going to try start to make use of this list i'm going to start to say something like this that means that this particular transformation is going to run on all of the columns which are mentioned in this particular list how do i write such a thing i'm going to use something like list dot transform and i'm going to say that hey why don't you pick up this particular list which is allocation columns so i'm just going gonna maybe say allocation columns and i'm gonna say that why don't you pick up every single item from this particular list and on every single item from this list why don't you apply this particular transformation i think this is good to go we can just take care of the brackets and stuff and let's just see what's going wrong right here so there needs to be a comma and i think this is good to go now what we have been able to do is that without mentioning the names of the columns i have been able to split the values of location business and the departments in a form of a list and i can just peek into the list and we are done with the first step now before we just maybe move to the next step let's just rename this step as split and let's just move further now the next step is going to be find out the values of this particular list count them multiply with this count them and multiply with this and again i do not have to write the names of the columns manually so let's just start with the next step all right in the second part or the second step of this particular query let's just start to count the number of items that are there in the three lists so in the location we have a list and i'd like to count the number of items multiply the number of items with the business multiply the number of items with the department and that is going to give me the number of rows in which this amount needs to be allocated how do we do that let's just start to do some manual work first add columns tab custom column and we're going to create a custom column called allocation in that particular column i'm going to write an underscore underscore simply means the current row of any particular context so it could be a table it could be a list it could be anything so i'm just going to say underscore for now i'm just going to say okay what do i get is a record record means the first row or the row of the data that's what a record means and if you take a look at this record in this record you're going to see that every single item of the first row is there so here we have sales erp that is right here we have 1.25 million which is right right here we have three lists and you can see the three lists right here location business and department but i have no business with the entire record right here instead what i'm trying to do is from this record i want to dynamically extract the location list the business list and the department list count the number of items which are there in this particular list and multiply them to be able to get the number of rows that is what i want so let's just start to do something manual and then we'll understand how do we go about it if i just go ahead to my formula once again and if i say that let's just start to extract the name of the column manually so i'm just going to use the function called record dot field and the record dot field function is going to ask you hey what's your record so record is nothing but the underscore you understand that and what is the field that i would want to extract for now let's say the field is manually typed which is location i'm gonna close the bracket and i'm just going to say okay and what do i get is the list which is the same list as what you would have it in the location column now i don't really want to have the same list but i want to dynamically not writing this manually i want to pull all the columns which are there in this particular list that i have created one by one and then multiply the count into one another and get the count that's what i want how do i do all of that i'm going to start to create a couple of variables and hopefully you'll understand so let's just start to write the let statement and i'm going to create a variable the variable that i'm going to create is let's say the record and the record is nothing but the underscore you understand that and i'm going to create another variable which is the count and now i'm going to start to get all the names of the columns from this particular list right so i'm going to use something like list dot transform now the list.transform function asks you for a list we already have a list right here so which is my allocation columns in this particular list what i'm trying to do is something like this i'm trying to go in every single list value which is nothing but the name of the column and for every single name of the column i'm trying to feed that into the records.field function how do we do all of that i'm going to say that each record dot field and this is nothing but the record because we have already captured that as a variable right here and comma is going to be an underscore because underscore simply means the individual item of the current row so the current row in this case is allocation columns which is right here the first item is going to be location the second item is going to be business and then the department all right so i'm just going to close the bracket close the bracket right here and let's just see what we get so i'm gonna say in and i'm gonna say the count and i'm just gonna say okay we again get a list but this time the list that we have received is not one single list but three different lists what are these three different lists these three different lists are the location list the business list and the department list and you can see that as of now i have not written the names of the columns manually anywhere it's all dynamic now i don't really want the list i want the count of the number of items in the list so what do i do i'm just going to go back to this formula and wrap this around in list dot transform once again so list dot transform and i'm going to say that hey here is a list and in this particular list why don't you uh count the number of items that are there in the list so list.count and underscore right here close the bracket pretty good and i'm just going to say that this function produces this list this list and this list for every single list that you have you just run this particular function which is list dot count simple i'm just going to say click on ok and now what i get is not the list but the number of items within that particular list now i just want to multiply these three numbers to be able to get that how many rows am i going to get so i'm just going to go back and i'm going to say something like list dot product and list dot is simply asking you that hey do you have a list which is which is containing numbers yes i have a list which is containing numbers and please multiply all the numbers one after the other that's it i'm just gonna close the brackets and hopefully this is going to give me the correct answer so 24 rows 12 rows 4 rows 4 rows 32 rows and 96 rows pretty awesome now what do i have to do i just have to take this particular value and divide that by 25 24 rows of data and i'm going to get the allocated amount so let's just do that i'm going to say that hey this particular account is giving me the count why don't you take the value column which is right here and divide that with the count so i'm just going to write the divide sign and this is going to be okay all right that is the allocation that is going to go for every single row of the data the last step which is where we have to expand all of these three columns but again we don't have to expand them in a manual way we have to ensure that we don't really write the names of the columns in any way and we have to refer it to the dynamic list that we have created let's move on to step number three all right the final step in which we have to expand but make sure that we don't hard code the names of the columns i hope you're still sticking around with me let's just get on with that now the job that we have to do is that we have the location the business and the department columns that we have to expand but make sure that the names of the columns don't get hard-coded within the query so let's just for now do some manual work try to take a look at the m code that gets generated and then try to work our way upwards from there so if i just click on the expand button right here expand to new rows i'm going to get a a function right here which is table dot expand list column that function asks you for two inputs which table are you trying to do the expand function in so i'm trying to do the expand function in the previous table which is nothing but the step and which column have i just expanded the location column is the column that i have expanded let's expand one more column so business column when expanded to new rows again the name of the table is the previous step and the column that i just expanded is the business column now note a very peculiar behavior the expansion that is happening or the step that is being created is always the previous step so this particular step is working on the previous step this particular step is working on the previous steps so on and so forth and the column name that is being hard coded should ideally be picked up from this particular list that we have created how do we do such a thing automatically keep expanding on the previous step automatically and still not write the name of the column manually so how do we do that i'm gonna get rid of these two steps and start to use a function called list dot accumulate new step i'm gonna use the function called list dot accumulate now i haven't really spoken about list dot accumulate on this channel i'll try to make a video on that but for now let's just try to make sense of it so let's start the first part asks you hey what's your list that you're trying to work with the list that i'm trying to work with is this particular list which contains all the names of the columns that i have to feed in to which function table dot expand list function so i'm just going to work with that list allocation columns then it asks you hey on which table would you like to start to work so the table that i would like to start to work is this particular table which is the previous step so i'm just going to put that as a seed table so added custom is the table then it asks you for two variables so we have a state variable and then we have a current variable and then i'm just gonna put that in a function and i'm just gonna say that table dot expand list column i'm just going to get rid of the table table again and in this function i'm going to say that the table is going to be the state so state and this is going to be the current uh now what does this function do so what i have done is i've declared these two variables which are the standard two variables that you declare in the list dot accumulate part the state the state actually means what's the table at every single stage and the current actually means every single row value or every single item value of this particular table right and the table dot expand list function picks up the table picks up the value and expands them one after the other and now we can see that in a single step all the columns have been expanded and the data is ready let's just call this column as allocation and let's just call this step as expanded and that is it now the benefit is that now if a new column is added to our data set we don't really have to do any manual work because in our query we have not really written the name of the column anywhere and all of this is dynamic and in case we have a new column that is added which is separated by a comma the allocation will happen automatically to that column in the number of rows all right people desert time now that you've stuck around all through this video you might want to take a look at the benefit of doing all of the hard work that we have done to be able to create this sophisticated query please take a look i've loaded the query in excel and we just currently have three columns in which we have to allocate the location the business and the department but tomorrow for some weird reason we get another column in which we have to start to allocate let's just say a category so i am just going to maybe mention a random category which is a sorry a comma c a comma c and press enter now the benefit is that because we have not really hard coded the names of the columns anywhere all of these columns are going to get captured automatically in this particular query and this query is the acting query which is going to initiate all of the steps that we have done in the advanced method right here take a look at the result i'm just going to right click and say refresh and a new column has been added which is a category column and you can see that the number of rows are double because every single category has got two values the number of rows are just going to be multiplied with two and that's all about it all right before we end on this video a big a big big shout out to mma 173 i just know the alias of this person i don't know his name and he proposed the solution that you have gone through with me just now the advanced solution it was absolutely brilliant you might want to give him an ovation and a big round of applause thank you so much for posting the solution and of course a big shout out to everybody else as well who participated and posted their solutions on the blog as well thank you so much and in the end i'd like to give a big shout about my tax and my power query courses in case you're starting out and you'd like to learn to solve hard problems from scratch build up your fundamentals first and then move on to solving difficult problems of your own data be tax or power query i'd highly recommend that you take a look at my courses it's going to be super super awesome thanks so much this was indeed a very very long video i hope maybe a bit too complicated as well let me know if you have any questions in the comments and i will be glad to reply thanks so much for sticking around all this while and i'll catch you guys in the next one [Music] you

Channel: Goodly

Views: 21,058

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

Id: t_PDveh-3Fg

Length: 21min 39sec (1299 seconds)

Published: Tue May 17 2022