PL-300 – Section 15: Part 2 Level 4 – Transform Menu Part 2

121. Unpivot

Now, in this video, what we’re going to do is to unpivot.

So, we’ve got a pivot table here, now to unpivot in Excel, if they still give you, whoever they is gave you the data, if it’s still in a pivot table form, then to unpivot all you have to do is double-click on the grand total to get the underlying table. So, I’ve used this when in an adversarial manner somebody gave me some data, they had deleted the original data (dialogue box dinging) or the data was giving me the pivot table, all I had to do to get the actual data was actually double-click, so it didn’t serve that person any purpose.

But what if, for instance, this was just the data that you got, you copied and pasted, this is it, there’s no way to get it back. What I want is three columns. The first column will show the date. The second column will show the region name, and a third column will show what it is I’m aggregating, in this case the sum of the sales volume. So, let’s see how this could work. So, I’m just going to close all my previous sources.

I’m going to delete them. So, there we go. And I’m going to open up the, data that we’ve just seen, pivot table, HPI Admin. So HPAdmin, house prices, H-P. So, the computer’s going to, misinterpret what it’s showing first of all because it’s got a row at the top that I don’t want using as a row, so again, as usual, going to remove a couple of the steps that are applied. Going to remove one of the top rows, so in fact, the top row. Going to use the first row as headers. Computer’s automatically, got the data type, so we’ve got date, we’ve got numbers. And now I want to unpivot the columns.

Now, you’ll notice, as opposed to pivot column, Unpivot Columns actually has a dropdown. So Unpivot Columns, Unpivot Other Columns, and Unpivot Only Selected Columns.
Now, you might wonder why there is Unpivot Columns, Unpivot Other Columns, and Unpivot Only Selected Columns, and the difference between these first two, and this last one, is the formula, and this could be quite a subtle difference.

Now, first of all, I can see that we have got column nine and column 10 included, I don’t want those included. So, I’m going to remove these columns.

Now, notice, I’ve got two options, Remove Columns, and Remove Other Columns. Now, if I remove the columns, you’ll see that the formula is table dot remove columns and it gives me that, and if I remove the other columns, and have to select of course, those columns to begin with, then it gives me a different formula. So, let’s have a look at these two formulas side by side and see how they compare. So, we have these, what are they, 10 columns, five, 10, yeah, columns, and we get to the same result. So, this first one, gets rid of columns nine and 10, so it leaves us with all of the other columns. This second version gets rid of, well in fact it doesn’t get rid of anything, it just keeps the other columns by name, and you can see, these have exactly the same results on the data that we’ve got. So, what’s the difference between the two? What happens if my data changes and I have a new column? Well, under this first one, I’m only getting rid of columns nine and 10 so this new column would remain. Under the Select Columns, I am saying which particular columns I want to keep, and so this new column, would not remain. So, at the moment, both of these formulas give exactly the same result, but when I add a new column, then the results differ. And that’s exactly the same, as what we have got in our pivot table. So, Unpivot Columns and Unpivot Other Columns gives one result, or one of those formulas, and Unpivot Only Selected Columns gives the other, so we’ll see which is which. So, I want to unpivot columns, So, what columns do I want to unpivot? Well, I want to unpivot Greater Manchester to West Yorkshire, so I want them to swing at 90% through. So, I’ve highlighted them, I go to Unpivot Columns, and there we have our result.

Now, you’ll notice that every other column, including the grand total, which I’ve just left there, just for interest, remains there, it’s just, the columns that I’ve highlighted, get pivoted through. So, previously we had, Greater Manchester, Merseyside, all as separate columns, all these six, and now all these six have been combined into two columns, what the heading was and what the value was. So, if I didn’t want the grand total, I could have deleted it beforehand, I chose not to, so, maybe, if I, delete it, so I’ll just edit the code that I’ve got previously, and then I’ve got to edit this formula, there we go, got date, attribute, value, and now I can rename attribute, and call that region name. So notice what we have, we have Unpivot Other Columns as the formula. So now let’s go back to our example here and instead of highlighting, Greater Manchester through to West Yorkshire, I’m going to highlight the date, in other words I’m going to highlight the column that doesn’t pivot and I’m going to Unpivot Other Columns. And again, you can see, it’s exactly the same formula, table dot pivot other columns, so it doesn’t matter whether you use, in this sense Unpivot Columns, or Unpivot Other Columns, you’ll get the same formula, it just matters what you highlight to begin with.

Now, let’s do the third example. So, just like at the beginning, I’m going to highlight these six, and I’m going to Unpivot Only Selected Columns, now let’s see what the answer is. So, the answer is exactly the same as before, but you’ll notice that the formula is quite different. So, now we are specifying, what is being pivoted, so Unpivot Other Columns, the first two options, we were specifying what remains, but here we are specifying, what is being pivoted. And again, what’s the difference between the two? Nothing at all, until and unless the data changes with a new column.

So, in this first example, new column would then be pivoted, or unpivoted, in this second example, it would remain the same, so it wouldn’t be unpivoted. So, if I go back to this and I will not include grand total in what I’m removing, so I’m just removing column nine and 10, but not grand total, you can see that, my unpivot now keeps grand total because that is one of the columns which is not being pivoted. However, if I just type in, my other formula that I’ve got, so just copy and paste that, you can see that grand total is not one of those which remains as a column, instead it becomes something that is pivoted, which may or may not be something that what you want.

So, unpivoting, you highlight the columns that you want to be merged into two columns, name and result, or you highlight all of those columns, and that’s either of those two, or first or third, or you highlight everything else, you highlight all of those that you wish to remain as a column. So, just to go through those two options again, you highlight what you, and I’ve just accidentally undeleted these, so I’ll just, do that again, I will, remove other columns, there we go. So either you highlight what you want to pivot into two columns, and go to either Unpivot Columns, or Unpivot Only Selected Columns. Or you highlight everything else, and go to Unpivot Other Columns. So that is how you can get some pivoted data, and turn it into unpivoted data, in two columns, one for what the column name was, and one for what the value was. And if you wish to, of course, you can rename this, either double click on it, and say, there’s my new region name, or what you can do is just change the formula at the top, so instead of it going to attribute, it goes to region name. And that’s how you unpivot.

124. Unpivot in Conjunction with Other Transform Features

Now, it’s a bit of a myth that you will get your data in exactly the right format that you need. It doesn’t happen, unfortunately. So, let’s have a look at this data.

So, this is pivot table HPAdmins2. It’s exactly the same as the previous pivot table that we were looking at, but with one exception. In addition to the Region Name, we’ve also got the Area. So, we’ve still got Date going down. We’ve still got some of Sales Volume in the middle. But going across, we have got Area, as well as Region Name. And we’ve got subtotals. So, let’s transform this data and unpivot it. So at the end, what we want are four columns. We want the Date, we want the Area, we want the Region Name, and we want the Sales Volume. So, let’s see how we can do that.

Now, we’ll be doing that by having the use of two things that we had a look at a few videos ago but didn’t really use. I said we would be using them later, well now is the later. So, I’m going to get my Power BI data, and I’m going to get the pivot HPAdmins2 and click Transform Data. So that gets me into this power query editor. So again, get rid of Change Type, get rid of Promoted Headers. We want to see the original data.

Now, we don’t need this top line, so this top line can go. So, we go to Home, Remove Rows, one row.

Now, let’s just have a look at the data we’ve got. So, we have got South Yorkshire, Tyne and Wear, West Yorkshire. They are all part of the north. And so what we want is North, North, North.

Now, you may remember that we have a Transform function, and I’ll just expand this so we can see all the words. We have a Transform function called Fill. And I told you that fills cells from non-empty cells into empty ones. So, that sounds like what we need. We want North to trickle to the right, and then Northwest can trickle to the right and so forth.

So, therefore, all we need to do is highlight this row and… Oh, can’t fill. Okay, well, we’ll just click on a cell and click Fill Right. Okay, there isn’t a Right. There’s only an Up and a Down. Okay, that really seems to stop that. Except it doesn’t quite. What we can do is flip this entire table around. We can transpose it. So instead of things going from west to east, from left to right, they will go from up to down, exactly how we needed it. So, let us now Fill Down.

Now, I don’t actually have to click on any particular thing. So, let’s click on Grand Total, just because it’s there. So, I Fill Down, and you can see that where there was null, we now have what’s immediately above it. So, let’s just have a view of that again. We have some nulls, and when we use Transform Fill, it fills down, removing the nulls.

Now, in Column2, we’ve got some nulls. That’s why we have the totals. So now, let’s replace the values. So Replace Value. So, I want to replace the null, which you can actually just type in N-U-L-L, just treat it like a string, and I’m going to call that Subtotal. So click okay, and now all these nulls are replaced with the word Subtotal. What I’m going to do now is retranspose it so it’s back to where we were, except now we’ve got all of this filled in. And just have a look at where we are. We have got one date going down, and we’ve got two fields going across.

Now, can we unpivot and have two fields as the answer? Well, let’s just try it. So, we highlight, let’s just highlight Column1 as the one that’s staying. And we’re going to unpivot other columns. And you can see the answer is it’s not that good. This attribute is what the column is, and currently, if I get rid of that step, the column is Column2, Column3, Column4, Column5, and so forth. So, I could use the first row as a header, but there’s nothing that says use the first two rows as a header. So, it doesn’t quite work. So instead, what I’m going to do is not transpose it. I’m going to leave it like this, this way. So now, we have two fields going down and one field going across. Can that work in an unpivot?

The answer is yes, as long as we promote Row 1 upwards. So, let’s promote Row 1 upwards, so we use the first row as a header. Let’s rename Column1 and Date, which isn’t Date now, so this is Area and this is Region Name. And now we highlight these two columns, and we unpivot the other columns. And now, we have got the Date, and we have got the Sales Volume. So, let’s just talk through all of these steps again. First of all, we started off with this data, with Area in Row 2 and Region Name in Row 3. We then removed the top row. We transposed it, so we now have Area and Region Name as columns. We filled down, removing all these nulls. We replaced the null with the word subtotal. We promoted the headers so that the date were right at the top, so instead of it being called Column3, it was now called the first of January, 1995. The computer automatically changed the type, that was fine. We named the columns, the first two, area region names that made sense, and then we unpivoted the other columns, and we renamed the columns. So, this is how you can deal with and unpivot where there is more than one field going across. More than one field going down?

Well, generally, you have the same problem as before. So, you have the problem where you’ve got all of these nulls. So, you have a heading and then some data going down. Well, you can just use the Fill function to go down. And it’s at this stage that you can now do your transformations. In fact, one of the transformations I will do is this Date column. It’s still text. I will change it to an actual date. And then we can Close & Apply and move to our dashboards as before. So next video, I’m going to give you some different data, and I’d like you to unpivot that data as well. Finally, there was an interesting error that came up when I tried to save this. It said that there were ten rows with errors in them.

Now, can you think under what circumstances we could have an error? I’ll give you a clue. It’s in the Date column. And if I keep scrolling down, and I do have to keep scrolling down to find it, here we can see our first error. And you can see it’s where there was a subtotal, where we put the word subtotal, but now it’s a date. So now let’s replace these errors. So, that is this one up here, Replace Errors. And what is the value that will replace the errors? I’m going to put the word null in. Because it’s a date, I can’t just have some text. It’s got to actually be something which is allowed in a date field. So, I’m going to put null, just click OK. And scroll down again, and you will see that we now have the subtotals with nulls.

So, the question is whether you actually do want to have these subtotals in. You could argue that it’s duplicating the data, and therefore it’s dangerous to have in, because you’ll have sold 817,000 units across the West Midlands, and then you’ve got it for each of the year, so it’s doubling the data. So that’s generally how I feel, to be honest, and so if I want to remove it, I could easiest way right-hand click on it, Date Filters, Does Not Equal. And that gets rid of the nulls. Again, if I scroll down, it’s no longer there. So, it’s the equivalent of typing in the word null in the Filter Rows. Right. That’s a look at the practise activity.

• Category: Uncategorized