DA-100 Microsoft Power BI – Part 2 Level 6- Add Columns, View and Help Menus Part 2

4. Index Column and Duplicate Column

Now, in the previous video, we saw that there was an error on row 721, but we may have been finding it a bit difficult to find exactly where row 721 was. So one thing we can do in our query is add a new column which just has a list of the raw numbers. And this is called an index column. Now, index columns, well, in real life, start with one. So row one, row two, row three, but you might want them starting from row zero, or indeed from row anything you want and going up or down in anything you want. So suppose you wanted it starting at ten and going up in twos.

Absolutely fine. So let’s add an index column to this. So starting from row one. And so here we have our row 12345, et cetera, and I’m going to call it row number and close and apply. So now we can more easily identify where row 700 and think it was 31, 21 is. And you’ll notice that the raw numbers in this visualization in this looking at the data, isn’t actually in raw order. So it’s a good job we’ve got this raw number. So we’re looking at raw 721, but we can’t actually express the query error because it tries to add a column called Raw Number, which of course we’re now using. So you can see why Raw Number is not the best column name. So I’ll call that raw number without a space. So let’s view the errors. So we can see Raw number 733. 734. They’re fine.

Actually, we’re looking at the errors. So those are 721, 722. So we can go down and find, or indeed even we could even filter on 721. And that gives us a more of a fighting chance to see what’s happening. And you can see month of house sales in this representation is blank. In reality, it contains the word error. So adding a raw number to most non trivial tables is actually quite a good idea, I think, at least at one stage when you’re trying to debug afterwards, it doesn’t matter where it is unless you are referencing it elsewhere.

You can just close it and hopefully you haven’t added any references to it. So you could just exit and remove the reference so that’s all index columns are, as I say, very useful for just finding out where you are. Duplicate column. It does exactly what it says. It creates a new column that just duplicates the values. And you can see the syntax table duplicate column. And this is the column that you are duplicating and this is the new column name. So that’s a couple of more duplicate general functions, index column and duplicate column. In the next video, we’ll have a look at custom column, and that is one that you could really spend huge number of hours on. We won’t spend a huge number of hours on, but we’ll just go over the basics of creating your own custom.

5. Custom Column – If Then Else

Now we ended the last video with this month of House sales and I had to comment that London is duplicated because we have in London and out of London and also England is duplicated. So what we could do is just filter them out. However, what if you actually needed those roles to remain but the month of or the House Sales sales gets nullified for that. Now, you could do this with conditional formula, with conditional column. But I want to use a custom column and custom column allows you to write chord essentially up here, except it’s in a dialog box. So we’re adding a nuke blank column and then we get to write in the cord. So let’s click on add column, custom column. First of all we get to name the new custom column.

So I’m going to call this house sales without totals. Now on the right hand side we have a list of all of the available columns and this is where I find this dialogue box is sadly deplete because we don’t have a list of all the formulas that you can use. However, there is a list when you start typing. So if it’s something to do with text, just type in the word text and you can see a list of the formulas. So text from and then open bracket and you get the autocomplete giving you the various syntaxes. But what I want in this particular case is if the region is London then give me a null and if the region is England, give me a null. Well, we’re going to write it exactly as I’ve said. So if notice this is a lowercase I and also notice it isn’t actually in the autocomplete.

So there’s a squiggly on the line. This might be telling you you’re doing something wrong. You’re not, it’s just waiting for something else. So token literal expected and good luck getting a real error to come up in terms of an explanation. So if region and it’s just simpler for me to double click. But if I started typing you can see that the computer does allow you to autocomplete by whatever you’ve got highlighted. Just press tab and it also completes.

So if the region is equal to and in double quotation marks. So London then null. Now notice the computer is trying to give you lots of other stuff. I’m just making this simple. Else if. Now two things. There else if are two separate words and I know some languages use else if without an E. It’s neither of those things. It is else space if. So else if. If the region is equal to can we use single quotation marks? And you can see no, it’s not looking as good, it’s not being treated as a literal. So we do have to use double quotation marks then null. Also notice I’m typing carried returns. I can do that.

So it doesn’t have to go on one really long line. Else give me the house sales and that’s it. So let me just comment on a few of these things. If and then and else are all lower case, no uppercase capitalization whatsoever, null is also lowercase. Also notice that unlike some languages, I have to end it. I don’t need an end. Also notice a lack of brackets. So in Excel, for instance, you would have if region equals London comma null comma no, none of that. It is this way. So I’m going to copy this.

I’m going to copy the text for a particular reason and click OK. And here is our answers. England null, london null, everything else brought through. Now it says added custom, but that’s not strictly true. Or at least that’s not how the computer is going to interpret it. Because when I click on the wheel, it gets me back to the add conditional column box.

So the computer has taken this chord that we’ve just written and when we’re going to edit it, it’s tried to give us a more user friendly code. So let’s just cancel this and add it again. So, house sales and I’ll just paste it in because I’m going to make a change. I’m going to say if the region name equals London or the region name equals England, then null else house sales. So you can use alls ands knots you can use greater than, less than, not equal to signs. And notice that the not equal to sign is a less than and greater than. So it’s not anything more esoteric that you may use in other versions. And notice that the equal sign is not two equal signs.

Again, if you’re coming from a language like C sharp, it’s one equal sign. So here we have if the region equals London or the region equals England in that case, then give me this answer, otherwise else give me this answer and you can do nested ifs and so forth if you so wish. So let’s click OK. And here we have the house sales of our total. And because we’ve used an or because we have used boolean logic when they click on the wheel, we get back to this custom column download box. So if then else very useful. You’ve probably come from a world which uses if in some way like Excel or Visual Basic or Csharp. So this is a way to be able to use your skills from that other language into M. So now let’s just see this in action. Click OK. Click close and apply. We go back to where we were previously, just loading some things. We still have the same error from before. Because I went back and recorrected it, I undid the correction. So here’s the error. So I’ll just go back and put it in.

So row begins with two and of course we can write this now using our custom column if you so wish. So go back to close and apply just wait for a moment. And now if you have a look at all of this you can see England and London are still there that’s because we’re still using the old metric of house sales but we now have this house sales without total measure so let’s put that in and we want a sum of that. It’s not giving us a sum of that that’s because we have got the wrong type, it looks like. So let’s edit this. So we change that to a one, two, three, a whole number close on applying change that now to a sum and here we have all of the data, london and England the roles are still there but they’re not being shown because they are in this particular visualization because they are a null.

So we’ve achieved the combination of having the data still be there. So here is the England figures but without them showing in our visualization and we’ve done that using if then else the else is opt optional. But I do recommend you always put in an house because the computer needs to know what is the answer if it isn’t this it needs.

6. Practice Activity Number 15 – The Solution

So how did you find this practice activity? So we’re developing our previous practice activity and we want to add a few things. First of all, we want to number the rows. So we’ve already got 1234, et cetera on the lefthand side. But we want an actual column where we are numbering the rows. So we add a column and it’s an index column and we’re starting from one. Now you could, if you want to do a custom and say you starting at index one and you’re incrementing, you’re going up in ones, both will be fine. So there is our index. Next we have got Mia Wilson and Miller Murphy being supervisors and the others being data analysts. Can we add a column showing their role? So there are two ways I can immediately think of doing this. The first is a conditional column.

So if I add an additional column, so this will be role. So if the username is equal to Mia Wilson, then make this person a supervisor. Alternatively, if the username is equal to Miller Murphy, then again make a supervisor. Otherwise everybody else we’re going to have as data analysts. So maybe data analysts singular. So there is our role and then we can edit it if we saw waste two later on. An alternative and something that gives us more flexibility is by using a custom column. So if we do this the first way, perfectly fine. This just gives you more varieties of being able to do this. So here we have the role. So if and the username is equal to and in speech marks, mia Wilson then supervisor else if. Two words. Now bear in mind if and else if and then all have to be in lower case. So if the username is equal to Miller Murphy then supervisor else data Analyst so click OK.

And that gets us that. And you can see if we went straight back into it, we would have exactly the same conditional column that we have done previously. Now we can simplify this and say if the username is equal to Mia Wilson or username is equal to Miller Murphy then supervisor else data analyst. So we’re not using an else if we’re just using an all. So that gets us again to the same results this time because we are using a more complicated formula, it’s not converted it into a conditional column. So that is our role. Next, we want to divide our log in times into morning, afternoon, evening and late evening, early morning. How many different ways can you think of doing this? And there are loads. So first of all, what I want to do is extract the hour.

So here we have the hour. So if it’s 01:00 p. m. , in this case 01:15, we have the hour of 13. So hour of login, let’s call that. So what we could do is add in a conditional column and say that if the hour so this is going to be the log in period. Let’s call it if the hour of login is greater or equal to 22, which is 10:00, then we’re going to call this late evening.

So let’s just do that and see. Here we go. We’re starting to get the results. Let’s go back in, add an additional clause. So that covers 22, 23 hours. Next we want to cover from 06:00 P. m. , which is 1800 hours through to 22. So if it is greater than or equal to 18, it’s got to be greater than or equal to because what if it is 18 itself, then this will be evening. If it is greater than or equal to twelve, then it will be afternoon. If it’s greater than or equal to six, then it’s morning. And we could do this in the else clause. But I’m saying if it’s greater than or equal to zero, then it’s going to be late evening, later evening, early morning. And I’m just going to have the same terminology in both here. So an alternative would be doing it in the custom column.

So you can see this is basically the column that I would be writing. And for me it’s not too great shakes to actually just write it by hand. But I can see there are certain advantages to being able to see it visually. Another alternative, well, we could enter the data into a separate query. So let’s do that. So we have in column one say our zero and that’s going to be late evening, early morning, don’t need that last column. And then 01:00, is that 02:00 free. This method looks a bit more unwieldy. However, it allows us perhaps greater flexibility. So if I just type in all of the numbers all the way to 23 and then we’re doing a lot of copying and pasting. And so there is our column.

 So this could be called over, called the other one login period. So let’s call this log in period categorization. So here is our new query. And then what we can do is go back quite a few stages and sections and merge the queries. So merge from computer login everything that is in the login period and put in the proper column names probably should have done. And then we can extract from that column two. And that is our logging period as well. There’s plenty of things that plenty of ways that you can do this. So the point of this is that there isn’t just one particular way to do anything. You’ll probably find there are a variety of ways, but just as long as you find something that works.

So I’m going to rename that as I think it’s this one. Let’s just have a look, see if I’ve broken the logic here is this one, I’m going to rename that as log in period two. There we go. So we can now expand our visualization that we did in the last practice activity. So we can be dividing supervisors from non supervisors. So I’m just going to duplicate this. So a quick way for me is to put in roll on top and in the legend.

So now we can see who the supervisors are and who the data analytics are. Alternatively, let’s go back to this, duplicate the page again. Maybe if I don’t have it so that it’s going down to the day, let’s keep it to the month, let’s have it as a stacked bar chart. We’ll put in the role again, but maybe I want to have a look at the logging period as well. So I’ll put that in the axis and then I can drill down further so we can see January afternoon, January evening, or maybe I want to go the other way. I have logging period right at the top and then if I wanted to, I can drill down into a year, into months and so forth. So I hope you found this practice activity interesting.

We numbered the roles, we added roles, we added in login periods. So you can see there’s a lot of functionality that you can get with conditional columns and custom columns, especially the if then else that manages to do an awful lot of things. So we’ve basically conquered the Power query editor. We have done the home, we’ve done the transform, we’ve done the add column. In the next section we’ll be looking at the help menu and the view view menu. And in a lot of applications, the view menu is fairly simple, not in Power Bi. So we’ll have a look at that in the next section. So you’ve.