Promote Double Headers in Power Query | Solution

hi you're watching the pad goodly once again and in this video we're going to talk about the solution to the power query problem that i gave you last week which is where we had to promote the double headers given in the data now if you tried but you weren't able to solve that's totally okay we're going to take a look at step by step how do we build the solution of the particular problem no further ado let's go all right just like a good cooking show what i have done is i have loaded the data here in power query and before i start to build the solution take you through step by step of everything that we are doing i'd like to explain you the broad logic of how we are going to manipulate the data to be able to get to the solution which is where we have the double headers promoted please take a look so this is the data the first thing that i'd like to do from this particular table is find a way that i should be able to extract the first two rows which actually should be concatenated and then promoted as a single header so find a way to extract these two so these two rows once these two rows have been extracted what i'd like to do is the two items of these particular rows which is product and the code should be concatenated as a single word maybe with a delimiter maybe not with a delimiter but they should definitely be concatenated as a single word now once we have been able to concatenate the data of the first two rows and create a single row or a record out of it we then need to find a way to be kind of delete these these first two rows of the data insert our uh concatenated first row in the data set and then promote the header so let's just see how can we do all of that first is extracting the two rows and converting that as a single row or a record that has concatenated both the values of the row please take a look it's not going to be that difficult all right so i'm just going to open up the advanced editor and in the advanced editor we're definitely going to write some good m code together so let me just zoom this in and start to write some m code the thing that i'm going to write let's just call this step as a header step so we are trying to create headers which is concatenation of the first two rows let's just call this as headers the very first thing that i would want to do is extract the first two rows of this particular data so i'm going to use a very simple function called table dot first n which actually allows you to extract the top n number of rows from a particular table so i'm going to say hey the table name is the source table which is nothing but the previous step you can see that here is the entire table right all of this is a table and this is also the name of the previous step so i'm going to say that the name of the table is source and from the source table i'd like to extract the first two rows let's see what is the output that we get i am just going to call the headers that we have created and say done let's just see what we get we made a mistake here let's just see what is the mistake that we have done we should not have applied comma in the end that's the mistake and i'm just going to click on done and we definitely get the first two rows of the data now once we get the first two rows of the data what i'd like to what i'd like to be able to do is just concatenate the two words which is product and code together nothing and the customer name class and this and just concatenate everything together to be able to do that what i'm going to do is i'm going to actually form a list and not just a single list but a list of a list once i'm able to do that you'll probably understand the logic why did i do it and how it actually works but let's just actually form a list essentially what i'm trying to do is i'm trying to form a list with these two words i'm trying to form a list with these two words a list with these two words a list with these two words so on and so forth so let's just see how can we do that i'm gonna go to the advanced editor once again and here i'm gonna actually wrap this table dot first n function into a table dot two column function so table dot two columns um i hope my spelling is right so columns and close that bracket click on done uh spelling was wrong i believe the c is capital not really sure let's just write again table dot two columns right that's correct and press enter now what do we get as an output of this particular function is a an outer list and inside of that outer list we also get sub lists right now if you peek into any of these sub lists what you're going to see is the two letters that we had it so if you just click right here this is nothing but product and the two product in the code this is nothing but null and the customer name uh class and null so on and so forth so you've got the two letters of the first two rows of the data what has essentially happened is that when we extracted the first two rows of this particular table and we had multiple columns every column that we had is now converted into a list with just two items right so that's what has happened in this particular step i think my covet is still there hopefully it'll go away nevertheless what i would like to do now is that a the first two uh the items in this particular list which is product and the code i'd like to concatenate that and create a single word which is product code and so on and so forth so how do i do that i'm going to go back to my advanced editor once again and start to modify my formula so i'm going to write something like this i'm going to say there is a list which i'm trying to work with and in that list i'd like to concatenate everything which is forming a smaller list so i'm going to write something like uh list dot transform now list or transform except accepts the first part as a list that which list are you trying to work with and what transformations are you trying to do in that particular list so i'm trying to work with this particular list and if you remember this actually gives us a list so if you think this is an entire list and in that list we have smaller lists and every list is going to have two parts which is the two rows of the data and we'd like to concatenate all of that together so i'm going to try to work with this particular list and in each sub list of the larger list i'd like to concatenate the letters that we have so i'm just going to say write something like text.combine text.combine asks you for multiple texts that you'd like to concatenate but those texts should be provided as a list not to worry we have a list which is packing two words that we would like to concatenate so that's not a problem so i'm going to say text dot combine underscore underscore means that uh this is the very item that we're trying to work with so every row is nothing but the very value that we're trying to work with and that is represented by an underscore so i'm just putting an underscore right here and i'd like to also put a delimiter which is going to separate the two words out just an optional and i'm just going to maybe close that bracket and let's just see what is the output that we get i'm just going to click on done now there is an error let's just go take a look at what the error is i believe i wrote the spelling incorrectly list dot transform list dot transform um text dot combine everything seems to be correct all right so maybe the spelling was incorrect so the list.transform gave us a list again but now you can see that the individual lists are gone and whatever items we had in that particular list they are concatenated with a little pipe symbol if you do not have anything to concatenate the word just comes as it is and all of them are now concatenated good now let's just go back to the source and take a look at our data and just try to recap the problem so far what we have been able to do is extract the first two rows of the data concatenate the items in that particular two rows together in inform a single list but now what i'd like to do is i'd like to delete the first two rows and just get rid of these two rows of the data the record or the list that i have created i'd like to place it here as the first item here and then promote the headers now there are just two challenges challenge number one is that if i'm trying to add a particular record here right add a record here it needs to be treated as a record record means the row of any particular table right every record will have two parts the record will have values that means what value would you like to have here so i would like to have broader code as a value and then it also needs to have the relevant header that means this product code as a value needs to go under which header it needs to go under column one as a header so what we're going to do now is that we've been able to form a list but we have not been able to form a record out of it right so let's just do that i'm actually going to go back to the advanced editor right here and try to convert our list into a record so let's just zoom in again and there is a formula called record dot from list i believe yeah record.from list and you can see that record dot from list asks you hey which uh list are you trying to convert it into a record remember that record is nothing but the row of the table it will have two parts the value and the header right so it's asking you hey what is the list that you're trying to convert it into a record and what are the headers under which the record is going to go so i am now going to go and place this right here in the data set just indent it and the next part is to add the headers of the data which we're not going to add it manually we're going to find an automated way to do that so i'm going to say that there is a table table dot column names column names there is a table which is nothing but the source table and which has some headers why don't you just pick up the headers from there so i'm just going to write the source and close that bracket and this is good to go now if i click on done what i'm going to get is product code which is a value that we are trying to place it inside of the table is going to be marked as column one and customer is going to be marked as column two and once we add that into our table they are going to go in the right positions now part one of the task is done let's just go on to part two which is where we are going to place our record into the table that we have created all right let's continue so we've been able to create the headers which is nothing but the record that we would like to add it to our table let's just try to edit this particular table remove the first two rows of the table and then add our record into this table how do we do that i'm just going to maybe come to the second step and come to the advanced editor and start to write the code further so what we're going to do is create another step so put a comma right here and create another step call this as an output table or something output table and i'm going to first of all extract the first two rows so that the first two rows never really appear in the table what i'm going to do is use the function table dot skip which allows me to skip the first two rows or n number of rows from the table the table name is certainly source s-o-u-r-c-e and the number of rows that i'd like to skip are two and i'm going to call this output table as my output and we are good to go click on done what i get is definitely the same data but in the output table we have skipped the first two rows of the data in this table that we have received which is like sense the first two rows what i would like to now do is insert this particular record as the first row of the data in this particular table right here that means here i'd like to add this particular step which is the header step how do i do that take a look i'm just going to go back to the advanced editor in the advanced editor what i'm going to use is a function called table dot insert rows insert rows right now table dot insert rows is asking you for a name of a table that in which table would you like to insert the rows so here is a table which is minus the two rows in which i would like to insert the rows then it says hey at what position in the table would you like to insert so i'd like to insert it at the zeroth position which is nothing but the first position the counting starts with zero all right and then it says that hey could you give me the data that i that you'd like to add it to the first row of the particular table and that data or that first row or record is nothing but the headers so i'm just going to write the headers right here but note that this headers is being asked as a list so i just can't write it as headers i have to surround that in the curly brackets whenever something is asked as a list so i am just going to supply that as headers just good to go and close that and let's just call the output table as an output so we have added the first headers rows in our particular data now the only thing which is remaining is just to promote this particular uh tables first row as headers and we are good to go so you can either do that from the user interface or we can do that using the advanced editor we have been using the advanced editor so why not use it a little more often so i'm just going to go ahead and wrap this entire function in table dot remote headers all right and just maybe format this a bit good to go and say done and the headers have been absolutely promoted so although we have done a lot of work but since we used a lot of m code and packed the functions together the output seems to be pretty small source table we got the headers right here and we created an output table which is where the headers seem to be promoted all right two more things before i close on this particular video point number one is that this entire thing that we have done so far can actually be converted into a function what is the function a function is something pre-built into power query and you can use that to solve you know scalar problems which is where if you have a similar problem or some other data set you can convert this into a function and then use that multiple number of times on multiple data sets if you think about it all that we have done in the entire m code that we have written we've actually asked multiple number of times for three inputs we have asked for a table the table that we are trying to work with which is where we are trying to extract the number of rows we are trying to promote the headers so on and so forth we have asked for a table so that is going to be one input we have actually supplied how many number of rows would you like to extract from the data set that could be the second input and the third input is a delimiter which is what uh you could be asked for uh as an input as well so if you actually like pack this entire m code as a function you can actually use that as scale for other problems as well i would highly recommend that you take a look at mma's solution i don't know the full alias of the person but he's absolutely brilliant at power query take a look at the blog comments and take a look at the m function that mma has written you're going to be pretty dazzled with that the second uh thing that i haven't been able to solve actually is that if you take a look the correct header should not be order sale state sorry the correct header should be order sale state but the due date is not the correct header the order due date should actually have been the correct header in the data now when we actually go ahead and import the data in power query uh because any merged cell comes as a null value and if we try to fill it down all the other null values are also going to be filled down that means product will actually be filled down here as well and this is going to be called product customer name along with order sales date and order due date and i don't really know that how do we like identify which are the merge cells and how do we conditionally find out that which values to be able to fill down as of now so i don't really have the solution for that at the moment i also believe that everybody who has provided the answers have actually made some manual tweak so i'm still waiting to kind of get that problem solved uh you know from either my side or anybody else's side all right that was it the solution to the power query problem i highly recommend that you take a look at the blog comments you're going to find some fantastic solutions a lot better than what i have provided here on the blog comments and you'll be pretty amazed with what you can achieve with the m code and if you have any questions around this please feel free to put in a comment on the blog or on the youtube channel and i'll be glad to reply in the end just like the way that we always do it a big big big shout out to everybody who has posted the answers uh on the blog or on the youtube channel thank you so much for being participative here uh if you were trying to learn power query or dax right from scratch and you want to build up your fundamentals first and then proceed on to solving more challenging more sophisticated problems of your own data i'd highly recommend that you take a look at my courses it's going to be highly beneficial thanks so much and i will catch you guys in the next one cheers bye check check check check check check check [Music] you

Channel: Goodly

Views: 24,298

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

Id: fql8M8qdVRw

Length: 17min 2sec (1022 seconds)

Published: Wed Jan 26 2022