Dealing with DOUBLE HEADERS in Power BI using UNPIVOT, TRANSPOSE, and FILL DOWN

in this video we're gonna talk about how to deal with double headers inside of power bi shall we listen to this guy let's go so let's talk about the problem of double headers and to understand what the problem actually is and how to solve it we need to first understand what unbuilding is let's use that as a starting point now let's take this table over here as our example where we have sales data for different products for different years now if you leave it like this and start working with it in power bi you might run into troubles very quickly let me show you why so here I have the same data imported in power bi so we have a corresponding field for each column 2018 till 2020 and a product column now let's create simple matrix visualization so let's take product I'm gonna put it on to the visualization now I want to show my sales values so well there's not just one sales field I have to show the sales values I have to grab 2018 put it in there 2019 put it in there 2020 I have to click on each year column to have them to my matrix visualization and if I want to show the grand total for each product well this is not possible hmm and what if I maybe not want to show the average sales over all of the years well we have a problem it's not possible or at least very difficult to do so how can you solve this now we need to unfitted our data so the data set that we have over here shouldn't look like this but like this now here we took our year columns 2000 18 19 and 20 and converted them into just one year column so we ended up with three times as many rows as before however our three columns are not just one so we have product year and sales now at this data structure we can work efficiently in power bi let's see this now here in power bi we have one field for each corresponding column so product sales and yeah so if I want to show let's say average sales amount for the different products I just simply go over here click on sales I want to break it down my product and here I can change the summary aggregation let's say average and additionally you also see that we have no grand totals and that was problematic before so how does this now relate to our double have a problem well if we want to untethered something then we just go over here to either the product column and then say and pivot other columns or you select the year columns and then say and pivot columns which then restructures the data set in a way that we need it to be where we have one product column one year column and one sales card with double headers he can adjust simply click on and pivot now let's see an example so here we have example number two where we have sales numbers broken down by the different products but now we also have over here the regions Europe Asia and then below it you have either 2018 19 20 just like before or here and replace it with revenues units and average selling price just different metrics so we have two headers one that shows the region one that shows the metric now let's use the pivot feature of power bi to improve it the data set that we have over here so here we are in the equivalent of power Mia I'm gonna go to transform and then make sure that the product column here the first column is selected and then under fit all the other columns and you see we have a product column okay that's fine however then we have a weird column where it says column 2 to column 7 okay and then we have over here a value code however I do not see any information on to what region and what metrics these values below that I have over here in the value column so something went wrong so let's see exactly how to fix this so I'm gonna start with the original data set here and when you're dealing with double headers then one of the things that you want to do before an pivoting is transposing which just simply means that you take the first row make it the first column second row make it the second column etc that's what I'm gonna do by going here to transform then click on transpose and now that we have first role as the first column we can do the column transformation fill down so that we fill up the missing data over you gone one so I'm gonna go to transform fill fill down now the first row contains information about the product selects make this first flow the headers which you can do by going to transform and then use first row as others alright so now we are ready to enter it so let's take our first two columns and then go to transform and unfit part of course there you go in the first column we have the region then we do not have the products but these are the matrix and then we have the product column and then we have the values for the corresponding matrix now if you say actually this is also problematic to have all of the values in one column and we're just to prefer to have revenue column units column a speak on we can do this no worries then you just go here to the matrix column and then you do pivot column so we do the reverse from before and we have as a values column then of course the value column and press ok so now we created our data set where we have the region product and then separate columns for each matrix revenues units and average selling price we dealt with first example of how to deal with double headers but let's now make it even more complex so here we have a table and we have similar data as before Europe Asia different metrics but we have now also here a product category column at the beginning and we have some total rows in between so here you see the data set and sine of power bi and we have column 1 which contains the product category now let's first fill up the missing data okay so I'm gonna go to transform fill fill down so that we feel everything down and so it fills up all of the notes that were there no second of all we have some totals over here that we want to get rid of so I'm gonna go here to column two and filter heart at the toilets all right so we cleaned up our data set a little bit let's now have a closer look so here we have your they're Asia then we have the second level of our headers which contains information on the metrics so we know that before in pivoting we need to use transpose so let's go to transform transpose and hmm something is still not right because here we have in the first round our product category and then in a second oh we have the product so again we have doubleheaders not to not to solve this well let's go one step back I'm gonna delete this nice stuff now what you do first before transporting is that we've gonna combine these two columns so that when we transpose we only have one row that contains the harder information before we done make that data okay let's see this in practice so I'm gonna take our first two columns then I go to transform and then over here I'm gonna use merge columns now let's put in a custom separator and this needs to be something that is not in the name of the product category or in the name of the products so this could be space and then pipe symbol and then space now in the new column name just leave it as merge that's fine all right and now we have all of that product info in one column now we can use transpose and the big difference from before is that all of this header information is now in one row what you then can promote to others so I'm gonna go to transform and then use first role as Heather's all right that looks already a little bit better now here in the first column we still have some notes let's fill this down so here we have the region then we have over here the metric okay and then we have all of the values okay so and these are the columns that need to be embedded it so I'm gonna take these first two columns go to transform and then and pivot other colors so now that we have our data under that we can go here to the attribute column and we can split it okay and we're gonna use a delimiter which is gonna be the pipe symbol that we use before to actually merge these two columns from before the product and the product category de alright so that was space pipe symbol space now here it doesn't really matter which one you take then click on ok now the first column over here is their category and then we have one column for the product and then we have our value column and here if we also want to pivot the different metric so that we have separate columns for each metric we just go you to metric pivot column and then use the value column and I'll use column and there you go we dealt again we have our double header problem and we have region category product revenues units in speeds so we have seen how to deal with double headers inside of power bi if you have any other related ideas then share them in the comments box below if you liked it and want to learn more about power bi and consider subscribing to our channel and give it a thumbs up thank you very much and see you in the next video

Channel: How to Power BI

Views: 8,341

Rating: 4.9870548 out of 5

Keywords: power bi double headers, power bi multiple headers, double headers, power bi unpivot, multilevel headers, how to use double headers in power bi, power bi data transpose, double headers in power bi, transpose data in power bi, unpivot in power bi, unpivoting in power bi, multiple headers in power bi, multilevel headers in power bi, headers in power bi, multiple headers, doubleheaders in power bi, power bi, power bi tutorials for beginners, power bi desktop

Id: VV7fRIkfVts

Length: 9min 50sec (590 seconds)

Published: Tue Jun 23 2020