DA-100 Microsoft Power BI – Part 2 Level 3 – Transform Menu Part 2

4. Practice Activity 11 – The Solution

So how did you get on with this practice activity? It’s a simple practice activity, but we are going to go deeper into unriveting in the next video. So it’s important that we’ve got this basic one done. So let’s have a look at our data source. And we’ll be looking at the Pivot HPI regions, so not regions two or admin ends with the regions. Now there are two blank lines at the top which computer ignores. And then we have got a further blank line at the top all bar the word sales, some of sales volume. So we need to get rid of that. So the computer is going to promote that erroneous, top line at the expense of this. So we don’t want the change data types, we don’t want the Promoted headers, we want instead to remove the top one row. Now we want to promote the first row, use the first row as headers.

And we have the unpivot here. So we can either highlight East Midlands all the way across to Yorkshire and the humba and unpivot these columns, or we can highlight the remaining column and go to unpivot over columns. Or we can highlight everything but the date and unpivot only selected columns, which gives us a different formula, which we went into last time. So depending on how you do it, there’s three different ways with currently the same outcome. Now, I wanted date, location and sales volume. So I’m going to change raw labels up here and I can change attribute either here, so I’ll call that location and you’ll see how the formula expands.

Or I could change it in here in the unproven other columns. Heaven’s sakes, don’t do both. But either way, you have now got date, location and sales volume as your three answers as your three columns. So now you can close and apply and apply the changes or it’s doing it automatically. And now you can create a dashboard based on this data. So this is down to you. This is a practice in visualizing. You could do a very quick visualization or you could do a multipage one, depending on what you want to do. So let’s just create a simple one to start with. Maybe a matrix with date going down, location going across and sum of sales volume in the middle.

So we’ve got totals there. And maybe we will add a stacked column chart as well on top. So one down here with the same sort of information. So dating access, location as the legend, sales volume as the value. So now if I click on any of these values, it gets highlighted, or if I click on out of London or any of the particular years, they will get highlighted. But suppose I don’t want them to be highlighted. Suppose I wanted this graph to be filtered, I could do that. First of all, I want some separation between these two. Visualizations a bit too close for my liking.

I’m now going to Visual Tools format edit interaction. And let’s say that when I click on anything in here, this bottom one, I don’t want anything to happen to the table. So I don’t want it filtered, because currently, if I click on something, the table gets filtered. I don’t want that, let’s say. So we click on none there. Everything gets driven from this table. And then from this table, I want it not to be highlighted, which is what’s happening now, but I want it to be filtered. So you can see it filters down to single value. But I could be filtering on West Midlands or Outer London or that sort of thing, or I could have out of London and Inner London if I hold down control while selecting the columns. So now I’ve got this and you can see why this amount of gap was needed.

If I had it really close in, then all of these would be on top of each other. No good. So now I’ll click away from edit interactions, I’ll deselect it. So now that allows me to do a bit more looking around. So now I can just look wherever I wish. So double click to get rid of the interaction. And let’s say maybe I want to add a slicer on the right hand side, which just has a list of the locations. So I could click on Northwest and it just shows northwest, so that could be a more user friendly way. So maybe I won’t have all of this being interactive. So if we get rid of the interactivity here, and I just have it coming from this slicer, and maybe I’ll add a second slicer above it. So I’ll just drag this down a second slicer above it, which has the time. So let’s put that all the way up here and we added the date to the field.

There we go. So now we can have everything. Or I can concentrate just on England or east of England, or I could concentrate on just one particular time period, or a combination of the two. And it could be that you want to have this as an initial dashboard and then you want some drill through to another page. Entirely possible, whatever you want. So, in this practice activity, we’ve taken a fairly basic concept. We’ve had data that was not really in a suitable format, we had dates and the one particular field going across, and then the values in the middle, we’ve transformed it into just three columns. And then we’ve created a pivot table, essentially, or an entire dashboard, which we can then use as the start of what we’re looking at. And we can develop it further and go down, drill down as much as we want. But for now, that’s the end of this practice activity. And I hope you’ve have generated an interesting dashboard of your own.

5. 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. HP admins two. 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 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 a use of two things that we had to look at a few videos again, but didn’t really use. I said we’ll be using them later.

Well now with the later. So I’m going to get my Power Bi data and I’m going to get the Pivot HPI Admins two 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 town and where 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 of the words. We have a transform function called fill. And I told you that fill cells from none 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 we 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 the 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 column two we’ve got some nulls, that’s why we have the totals. So now let’s replace the values. So replaced FIU.

So I want to replace a null which you can actually just type in N ULL, just treat it like a string and I’m going to call that subtotal. So click OK. 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 let’s just highlight column one 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 column two, column three, column four, column five 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 one upwards. So let’s promote row one upwards. So we use first row as a header. Let’s rename column one 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 two and region name in row three. We then removed the top row, we transposed it. So we now have area and region name as columns. We filled down removing all of these nulls. We replaced the null with the word subtotal, we promoted the headers so that these dates were right at the top. So instead of it being called column three it was now called the 1 January 9095. The computer automatically changed the type.

That was fine, we renamed the columns, the first two area, region names that they made sense and then we unpivoted the other columns and 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 then 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 transformation I will do is this date column. It is still text. I would change it to an actual date and then we can close and apply and do boot 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 say this.

It said that there were ten roles 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’ll see that we now have these 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, have the easiest way of writing, click on it. Date filters does not equal and that gets rid of the null. So, again, if I scroll down, it’s no longer there. So it’s equivalent to typing in the word null in the filter rows, right.

6. Practice Activity 12 – The Solution

Right, so how did you do this? Practice activity. So pivot RPI regions two. Now, notice that this preview is truncated due to size limitations. It doesn’t contain more than one row. It can’t get down to the number of columns as it needs, presumably. So let’s transform this data and see what we got. First of all, yet again, we’ve got the Promoted headers and the change types. We don’t want that. We do want to remove at the top row. It’s quite frequently pivot tables. Now we’ve got years running across in row one and months running across. This time we haven’t got any subtotals because this is an average price. I suppose I could have put an average price in as I wished, but I chose not to. And here we have the region name. So we need to fill in 1995 going across. So therefore we do have to transpose the data and now we can fill down. So what we want is year and month in two columns. We want the region to be in a third and we want the average price, which is what this number is, in a fourth column. So we need to promote this row one into a header, so that the header contains the region name.

Then I’m going to rename at this stage the columns, because at the moment they’re a bit misleading. So here we have year and here we have month. So let’s highlight these two columns and say, I want to pivot the other columns, so that gives us the region and the average price. I’m doing that in the formula. You can rename them any way you want, but the average price is not yet a number and I can’t convert it into a number.

If I try to go to a whole number, you can see it just brings lots of errors. It’s prefixed with GDP space, so we need to remove that. So we go into the replace values. I want to find GDP space and replace with absolutely nothing. I don’t need to match the entire cell contents, I just want to replace just that part of a cell. So let’s click OK, so now I can change it into a number and that is much better data. So now if I use this to create a dashboard, so the changes are being applied.

So what I’m going to do to start off with is I’m going to create a donut ring, it’s a donut chart, with the region being the various colors, the legend and the average price being the value. Now, you could argue that we should have the average of the average price, so I’ll do that there. So what I’m going to do with this is have this as a drill through. So when you click on any one of these, it drills through to another page and this other page has a let’s call it have a line chart. So we have a line chart which has the month as the axis and the average price in the values. In fact, we also need the year in the axis as well. There we go. That makes it better though. That was interesting.

If I removed or if I go down to the month, you can see that the months are in a declining order, they’re based on the average sales price and you can see it’s cheapest in January and generally most expensive in December over all of these years. So there we can see all of them going down. It’s not quite right, it’s not in the right order, but we can correct that in the next video. So now we need to do a drill through. So if I click on any one of these, I’ll click on drill through. So nothing’s working at the moment. But now, if I go to page two and go to the drill through, I am going to drill through the region. So now, when I go back here, I can drill through to page two. So let’s call that region analysis. So click on one particular region. This happens to be London.

Click on region Analysis and there we have our region analysis. Now there has to be a lot more work in this actual data. If I was doing this for real, I’ve got London, I’ve got out of London and I’ve got inner London in London plus out of London equals London. So really what I need to do is exclude London. Similarly, I could add a drill through page with year. Just one caveat, because it is being used as a measure. You can see that with the sigma sign next to the word year, we’ve got to change the drill through from allowed drill through when summarized to used as category and then we can do the drill through. So, hope you enjoyed this practice activity and I’ll see you on the next video.