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

119. Transform – Table and Any Column

In this section, we’re going to have a look at the transform menu, and specifically, tables, any column, text column, and number column. There’ll be some revision in this, partly because there’s revision in here already. A lot of this is in the home menu that we had a look at in the previous few videos. So, I’m just going to load just for this video, the CDs and just have a look around at the start of the transform.

Now, you can see that it’s promoted headers and changed types, but there’s three header rows at the beginning that we don’t want to have the computer ignore. So, I’m going to click xs onto those, I’m going to remove the top three rows, now, I’m going to promote the headers. I’m going to use the first row’s headers. But, instead of using it here, I’m just going to show you it’s exactly the same in the transform menu. I can use the first row as headers there.

Now, the computer has changed the type, but if you didn’t want that to happen, then you can use the data type here as well, so I could say, this is going to be a whole number or decimal number, and you see the little icon change. And, of course, you can use the icon at dropdown here as well instead. You can also get the computer to say, “Well, what’s your best guess? “Can you detect the data type?” So, if I was to hold onto these three columns by clicking on the first and then holding down Shift and clicking on the last, I can detect data type, and the computer says, “Well, I think they’re text.”

Now, if the computer gets them wrong, then you can always change them. So, for instance, suppose I say, “No, this isn’t text. “This happens to be a date time.” So, there it is. The computer’s getting an error, but the fact is, you can change it. So, you can see, it says date time there, so we can go back to the previous one and say file extension, instead of type text, it’s type date time. There we go.

Now, just starting from the left-hand side, we’ve had group by, we’ve already used group by before, first row of headers, just skipping a bit, rename, you can rename the column’s names, or you could do that by just double-clicking on the top, so this could be file name brackets text. So, you could do that by clicking either rename or just double-clicking the header. Replace values, we had a look at replace values earlier, but I mentioned at that we could also have replace errors in the transform menu, so you can see all of these errors here. So, if I wanted to go into replace errors, and I’ll just replace them for blank, but you could replace them with a value if you so wished. In fact, you can see for date time, I can’t actually replace with blank, it says enter a time value, so I’m going to enter A time value, so, let’s say, 2021-01-01, see what it makes of that. There we go. Move this just moves left to right to the beginning to the end, so we’ve seen similar things before just by dragging. So, now, if I get rid of a few of the crazier things that I’ve just done. Transpose, that transposes tables, so, in other words, rows become columns and column become rows. So, there you can see the result. And we’ll be using some of these in future videos, so I’m just going through what they are, but as for a practical application, we’ll have a look in a few videos. Reverse rows, well, that takes last to end, I mean, first comes to last and the last comes to first. Not often you would be using that in concluding account roles.

Again, not often you’ll be using that, either. Fill, what fill down and fill up does is that if there are blank rows, or blank cells, it will flip down, and so, for instance, suppose you had a header, and then some blanks, which all relate to that header, then you could automatically fill down all of the blanks with whatever’s above. Not often you’ll be using fill up, there may be a few situations later. And convert to list, so that just removes other columns, so convert to list, we just now got just the one column, for instance. Not often you’d be doing that. So, table and any column, most of them, you’ve had before. What we’ll be looking at in the next few videos is pivot and unpivot, and we’ll be using a lot of what we’ve got here to help us do this.

So, this was a quick run-through, but a lot of stuff you’ve had before and some like, for instance, transpose and the fill, we’ll be having examples in the next few videos.

120. Pivot Column

In this video, we’re going to have a look at pivoting. And it’s probably useful to have a look at the data that we’re going to use first and see an example of pivoting in Excel. So, here we have the table HP Admins. So, this is a series of dates, so January ‘95, February ‘95, March ‘95, a series of regions, and over here, the sales volume.

Now, it’s quite a lot of rows. So, you can see 1,585 rows in total. And it’s very difficult to get your head around what the message is actually saying. So, you could do various pivots. Look at one particular area and go, okay, it’s increased here. In March, it was really good. It fell back in April, maybe, because Easter. But all of this analysis takes time. So, what pivoting does is allows us to get a report of this data.

Now, the word pivot is a central point upon which something turns. And so what will be happening is the date going down, the region name going across, and then in the middle, we’re going to have the sum of the sales volume. So, let’s do this in Excel to start with. It’s very useful in Excel, far less useful in Power BI. And I’ll get to the reasons for that later on in this video. So, I’ll go to insert, pivot table. And I’m going to have date going down. I’m going to have region name going across and sum of the sales volume as the values. So, we have rows, we have columns, and we have values. So, here, we can see, very quickly, Greater Manchester, the sales really started to take off, 2004. There’s a bit of a hitch in 2005. Might be worth investigating why that was. 2006, 2007, similar sales volume to what we’ve had for five out of the previous six years. And then suddenly collapse with the financial crisis in 2008.

Now, in Excel, we can then go deeper and deeper. But this is not an Excel pivot table course. If this is new to you and you do want to find more about pivot tables, they’re very quick, very easy to use, very powerful. And I can recommend you searching for a particular course on that. So, this is how you do it in Excel. And I said, there are three major components, rows, columns, and what you’re actually aggregating, your measure, in this case, what I’m summing. So now let’s have a look at it in Power Query. And I’ll go into the reason why you probably don’t need to do this often. However, this is quite useful information to know because then we’ll be able to talk about unpivoting, which is much more useful. So, I’m going to load the same data that we were looking at. So, this was the table HP Admins. Here it is, so I will load that. So, the computer has promoted headers and changed types. Fine. And now let’s click on pivot column. So, what I’m going to click on first is what I want in the columns.

Now, you will see soon I’m actually missing a step, and, but it’s important to see what happens if you do miss a step. So, I’ve highlighted region name, which is what I want in the columns. So, if you remember, we have all of the region names going across. So, click on pivot column, and it says what is it that you want to aggregate? What’s your value?

Well, in our Excel example, our values was the sum of the sales volume. So, I will change that to sales volume. And you’ll notice there are advanced options. (laughs) They’re not really advanced to be honest. It just says, what do you want to do with sales volume? Do you want sum, average, median, so that’s the middle value, min, max, or count? Quite frankly, that is not (laughs) that advanced. So, I’m going to click OK. And what I want is what I see here. But I’m not going to get that because it’s going to treat all of the other columns as being what I want going down.

And of course, there are a lot of other columns. So, before pivoting, what we need to do is remove any columns that we don’t want. So, I’m going to highlight the columns that I do want. Can you remember, and I’m using Ctrl to highlight them, how to remove the other columns? We go to home, remove columns, remove other columns. And you can see the formula Table.SelectColumns. So now I’ll go back into region name, and I will pivot this column against the sales volume, sum of the sales volume. Click OK, and now you can see what I was anticipating to get. We have the date going down. We have the regions going across. We don’t have a total like we did in the Excel version, but that’s okay. We don’t have totals at the bottom either.

Now, I’m just going to duplicate this. And I’m just going to leave it alone as it was to begin with. So, we’ve got these two. We’ve got this version, which is the pivoted version. So, what we’ve got is date running across, regions running across, and sales volume in the middle, and the unpivoted version. So, what we’ve got is every column describes a different thing, whether it is a date, whether it is text, and what’s the where, the when, the what, how much or that sorta thing. So now if I close and apply, we can see that the two new tables, queries are being added. What I can do now is add these in, say, to a table. So, if I create a table, and I will add date. Maybe, I don’t want to go down to such levels. And then I can add in Greater Manchester, Merseyside, and so on. So that gets me the representation of the pivot table that we had earlier.

But the question is how useful is this at this stage now we are doing the visualisation? After all, if we have the unpivoted version instead of using a table, I can use a matrix. So, if I add a matrix in there and I have the date in the rows, I have the area, the region name in the columns, and the sum of the sales volume in the middle, then we will get to the same answer. If I just show it by year, there you can see it’s identical, and I’ve got totals. So how useful is this really? Well, I suppose, if you said, well, I don’t actually want, in this particular example, to concentrate on all of these particular areas. I just want to concentrate on Greater Manchester and South Yorkshire. So, you can see it might be able to make that a bit more quickly. But hang on. In this matrix, I can just apply filter and say region name is, and I want the region name to be Greater Manchester and West Yorkshire. There we go, job done.

So, pivoting is not as useful in Power BI as it is in Excel because there are very powerful methods of being able to get to the same result without having to go through pivot tables. Indeed, what the matrix does is itself a pivot table. It’s got this going down and things going right. So, this is less useful than in Excel.

It’s important to know, however, that you can do it because in the next video, important to know the concepts and so forth, in the next video, we’ll be at the reverse. And this is much more common in Power BI. We have got some data in a standard format. One column each describing something unique, each being quite straightforward. And we’ve just pivoted it. But what happens if the source data that we’ve got is already pivoted? It is in this pivot format. And we actually want it to go back to something like this. And that is going to be the subject of the next video.

• Category: Uncategorized