DA-100 Microsoft Power BI – Part 2 Level 4 – Transform – Text and Numbers Part 2

5. Transform/Add Column – Number Column – Statistics and Standard

In this video we’re going to have a look at the transform or add column number functions. And there aren’t too many surprises here. So first of all we have statistics. So some main max medium average standard deviation count values and count distinct values. So what that will do is transform what you’ve got into just that. So here we have the sum of the average price. Now, the formulas that they use is not that surprising, it’s list sum min max median average standard deviation, non all count. Now count distinct values will look at a particular column and you can see this can be worked for text columns as well and see how many values which are unique. That’s to say they can repeat but ignore all the repetitions. So in this case the answer will be twelve because there are twelve regions in our data. Now, going to the standard we have add, multiply, subtract divide, integer divide. So what these first four does do is they change the column in this case or add an extra column which shows what happens if you divide a particular number or multiply a particular number.

So let’s say this average price is in pound sterling and we want to multiply it by 1. 3, let’s say to get an answer which is in dollars, well I could add a column multiply and it says what do you want to multiply by? In this case, 1. 3. Click OK. And there we have our new price. But suppose we had two columns, so here we have the price in dollars and the price in pounds for instance, and we wanted to divide each of them. So we wanted to go what is dollars divided by pounds? And this is something that you can do as well from here. So again, standard divide. But instead of dividing by 1. 3, we want to divide by pounds, want to divide by another column and that’s why we click on this ABC one to three, which is a fixed figure like 1. 3 or hello or something like that. And instead I will say use a column and I want to use a column pounds. It’s only giving me the columns that make sense. I can’t divide dollars by region 59,000 divided by East Midlands doesn’t make sense. So when I click OK, then we get the answer unsurprisingly of 1. 3 with a slight rounding error in some cases.

And you probably will not be too surprised to learn the sign is exactly the same as it is in Excel plus minus multiply divide divide integer, that’s the same as the divide except it gives a whole number. So for instance, if I was to divide the dollars by two, let’s say I wanted to get integer number currency, but I just wanted a whole figure at the end, I don’t want all these decimal points, then that’s what integer division can do. And also notice we’re not using the divide sign this time, we’re using number integer divide. Modular is the next one. And what modular does is it takes the remainder. So let’s say I wanted to divide this by ten, so no problem, I can divide that by ten or maybe 1000. Let’s say I wanted this figure to be expressed in thousands, that’s fine, but I actually want it to be in a whole number. Well, I’m going to change that from a division to a division integer.

So let’s divide that again. Or I could obviously just change the formula so that gets me the number of thousands. And to be honest, I wouldn’t do the renaming that way. I would actually just edit the formula that just assured that a lot of stuff can be done with just using the GUI, with using the interface that we’ve got. But then I want the remainder after divided by thousands. So that’s the modular. So I want to find the remainder. And again you can use another column if you want. And so this number is 59,000 plus 208. 5. The last two functions, percentage and percentage of are merely versions of things that we’ve seen. So for instance, I want 50% of this pound. Well, you could just multiply by 50 and divide by 100 and that’s what percentage does. So I want 50% of that figure.

There we are, 22,700 and 6772, which is multiplied by 50 divided by 100 and percentage of is similar what percentage of the pound dollars? What the percentage relationship between them? So percentage of dollars of the pounds will come up to 130 because it’s 1. 3 relationship. So again, all of this just very similar to what we’ve already done, just expressed in a convenient form, if you so wish.

6. Transform/Add Column – Other Number Column functions

The scientific and trigonometric values are functions, absolute values. So that takes away the minus sign at the beginning, power, so we can square it. In other words, multiply it by itself, we can cube it, multiply it again. So three times or as many times as we want. So this is number, number ABS, number power, square root, number sqrt. Slightly different functions you might be expecting. So that is the equivalent of power to the half. So four squared is 16, the square root of 16 is four. And then exponent logarithm, you know if you need them, whether you need to use E or logarithms. If you don’t, you will never need to know that it’s EXP or log ten or Ln. And then factorial five, factorial is one times two times three times four times five. So I would say these first three much more often used than these last three. Trigonometry, sine cosines, tangent, arc signs, arc cosines and arc tangents.

I’ve never had to use them in real life. But if you need to use sin cos, tan asine a cos and a tan, then they’re all number dot functions, rounding, you can round up, you can round down or you can round to a multiple. So if I wanted to round up, that just rounds up to the nearest hole number. Rounding down will round down to the nearest hole number, but round dot, dot, dot, how many decimal places you want to round to? So let’s say I multiplied this by 1. 1, or let’s go for an example, 1. 3 to get the dollars. So I’ve added a new column, but then I’m going to transform this column, so I’m not going to create yet another new column and I could just round up.

So let’s take 57145 dot, four becomes six, so it rounds up or come round down. So that just truncates it. In this case, if there are negative numbers, then it would go down to the down to the next one, or I could just round. So with a zero decimal places and in fact I’ll just add a column to do this particular example. With zero decimal places, you can see that if it is zero five or greater than zero five, it rounds up. If it’s lower than zero five, it rounds down. If it is exactly equal to zero five, well, it looks like it’s rounding down and this looks like it’s rounding down as well, this one here. But if I go down a bit further, this one rounds up. So what actually happens is it rounds to the nearest multiple of two, so you’ll get an even number at the end. So above zero five rounds up, below zero five rounds down, zero five, exactly. It rounds to the nearest even number.

Now, you could, if you wanted, not go to zero decimal places, you could go to one decimal place, which in this particular example would keep us exactly where we are or you could go to minus one decimal places so that gets us the nearest ten. Or you could go to the minus three decimal places that will get us to the nearest thousands. So the functions there are number roundup, round, down or round. And then finally, and not so often that you’ll need this at all is the information. So we have a question of is it even, is it odd? And what’s the sign that I suspect is used more than is even always odd? I quite often use it in terms of using it with the absolute value. I want to know if it’s negative or positive or zero. So that gives me a boolean a true or false value. So this is a quick whistlestop tour through the number. There’s nothing really earth shattering in all of this.

So the standard will allow for mathematical functions. Scientific will allow functions like absolute power, one square root trigonometric are all to do with angles. Rounding gets us to nearest whole number or the one immediately above or one immediately below or two nearest ten to nearest ten. And information is even, is odd and sign.

7. Practice Activity Number 13 – The Solution

Hello. And how did you do with this practice activity? In this section, what we’ve been doing is transforming or adding text and number columns. So I asked you to get from your standard data source the Power Bi data, the computer login spreadsheet. I’m going to transform that rather than just log it. So the username has got some hidden spaces. How can we get rid of them? So, for instance, it might be Noah space, Cox space. And these spaces are going to be really bad for when we have a look at the next item. So we’ve transformed and it would be in text column somewhere, and it’s actually in the format section. And we’ve got trim. So trim, as you can see, removes leading and trailing white spaces. So there we go. Now, if you find that you have got some spaces in the middle, double spaces especially, then what we can do is go into replace values and say, I want to replace two spaces with one, for instance. So trim, as you see, just to get the leading and trailing the beginning and the end. But that’s sufficient for what we need.

Next, can we add three additional columns for first name, last Name and Middle Name? And you’ve noticed I’ve made it harder because not everybody has a last name. Most people obviously have. Everyone has a first name, but only a few have got middle names. So let’s add a new column and we are going to extract from this text before a delimiter. And the delimiter is a space. So let’s click OK and see if this has worked. So we have got the text before delimiter, which we can rename. I’m going to rename it now. Call it First Name and you can see it has worked. So noracocs. Nora luna cox luna. Harper is Harper. And going for somebody with three letters, three names. Abigail is Abigail. So that’s fine. Now, an alternative, of course, to doing the renaming and then the reordering is changing the formula here. So we change this formula so that the name is or the resultant column is called First Name, and then we can move it. So that’s the first bit done.

How do we do the second bit? Well, let’s try and work this out. One possibility could be, instead of going from the beginning and getting all the text before the first base, we’ll go from the end and get all the text after the last base. So let’s just try that. We’re going to extract text after delimiter. We’re going to have the space, but I want the last space, not the first space. So what I’m going to do is go from the end of the input and go back one space. So let’s just rename this again. So I’m going to rename it Last Name and see whether it’s worked. And superficially, it looks like it’s worked. Sonora Cox, Luna Cox. We go to somebody who’s got three names abigail Jane Bailey’s. Abigail Bailey. Brilliant. We go to somebody with one name, Skylar, first name Skylar, last name Skylar. It’s not worked. There were no spaces. And so it’s just given me the totality of the data.

So unfortunately, a great idea hasn’t worked, but keep that in mind. So what I’m going to do now is get all of the name after the first name. So I’m going to do the text after delimiter, almost exactly what we’ve done. But instead of going from the end of the input, we’ll go from the start of the input. So this gives me our text after delimiter. And so that gives me middle and last names. So it doesn’t just give me the last name itself, it gives me the middle and the last name. So let’s have a look. Nora Cox. Luna Cox. Skylar Blank. That’s what we need. And somebody with three names, abigail Jane Bailey. Now that’s what we want. So how do we get the last name? Well, we do exactly what we did in a prior example. Here. We’re going to extract all text after the space at the end. So where we have Jane Bailey, it will capture Billy, where we don’t, then it will capture the entirety of the name. So here we have our last name and I’ll drag this in. So now we have got Nora Cox, luna Cox and Abigail Bailey. Now, as I’ve said in the instructions of this practice activity, getting the middle name is actually quite complex because we don’t actually have lots of people with three names. And that’s it.

If we did, then what we could do is we could extract text between the delimiters, so I could have from the first base to the next base, for instance. But that won’t work in this case because we’ve got all of these surnames. It’s nice to get Jane correct. And if everybody had three names, that would be brilliant. But that’s not how this particular works, where we’ve got a variable number of names. So I’m going to do this at the end of this solution. It’s to do with formulas and editing formulas. So if you’re not interested in that, then you can skip it when we get there. So, continuing in the first time zone, please remove the colon zero and convert this column into a whole number. So am I adding or transforming? I’m transforming. So let’s go into the transform column. And we have got the replace values. So I’m going to replace call on zero zero with a blank.

So we now just have minus zero six, plus zero two. And if we have a look at the filters, you can see that’s what we’ve got. It says it may be incomplete, but that’s okay. And we’re going to convert this into a whole number. So now we’ve got minus six, minus eight two. Now this time zone shows how many hours there are ahead or behind granwich. Meantime or the universal time. Now can you convert this into a number of minutes by multiplying 60, so we can do that in the number column? We can do this in the standard, so you can see the plus minus divides and times. Gives you a really big clue. It’s in the multiply section. And I’m going to multiply this by 60 so we can change this if we wanted to. So times on minutes. And now let’s create a visualization showing the average number of minutes before or after Universal Time or Greenwich, meantime, per user.

So we could do it a number of visualizations. I’m just going to do a fairly boring table perhaps because there may be a lot of users and I’m going to put in the username, I could put in the first name, I could put in the last name while I’m at it, or whichever you choose to do. Absolutely fine. So we can see a lot of people with no last names all grouped together and I’m going to put in the time zone in minutes and I currently summing them. So in other words, if somebody is 2 hours ahead and has got 20 entries, it will probably be saying it’s 40 hours ahead, which is 2400 minutes. I don’t want that, I want to average it instead. So here is our average of time zone.

So you can probably tell these people go all over the world and some say largely to the east, some say largely to the west. So what I’m going to do is I’m going to copy that and let’s create a bar chart with that information on as well. But for the bar chart, we probably should have the full name, the user name instead of the last name because it was combining all of these together, for instance, in one. So hopefully in this practice activity you are developing your skills in transforming and adding text and number columns. So in the next section, what we’re going to be doing is looking at dates and times. So if you don’t want to do something complicated with formulas, please skip to the end of the video and I’ll see you there. Right, so how can we get the middle name? So for most of these people, the middle name will be blank. Well, we have got the middle and last names. That is a really big help to us. And what I’m going to do is I’m going to extract the length of this.

So this is the length of the mid and last name. So here we have three, six, seven and zeros, where they don’t have a middle last name. And then I’m also going to extract the length of the last name. So so this is the length of just the last name. So really what we want is to extract the start of this middle last name, but exclude the number of characters that the last name takes up. So let’s have a look at this example. Jane Bailey, she has got eleven characters in Jane space, Bailey, and her last name, Bailey has six characters. So we want to extract eleven minus six equals five. So what we’re going to do is highlight this and I’m going to extract the first number of characters. I’m going to extract one character and I’m going to call this the middle name. So that hasn’t looked at length mid last or length last. So what we need to do now is change this. So we are looking at length mid last.

So we’ve deducted the one, we deleted the one that was there and we’re saying instead of just a number one, we are going to have the length that we’ve got minus the length of the last. So you’ll see for the vast majority of people it’s disappeared. But when we get down to Jane Bailey, it’s fine, we now have Jane, so we now have Abigail Jane Bailey. And the more eagle eyed of you may have worked out that it’s not actually Jane, but Jane with a space after it. So if you want to get rid of the space, then you could subtract another one. I don’t recommend that because then we get all of these errors because for these people are trying to extract minus one characters, which doesn’t work. The alternative, we go right back to the beginning of this practice activity and we just trim. So now we have got Jane and D, but instead of adding a column that I’m going to transform it.

So now the length of this column is four for Jane instead of the five that we previously had. So I hope you enjoyed this practice activity. I hope it wasn’t too difficult to get your head around how to extract the middle name, or at least if it was difficult, it won’t be now. In the future, if you come across a similar situation, what I’d probably do now is hide or delete the middle and last name, remove it, it’s not needed. And you’ll see that the middle name still works even though it’s referring to a middle and last name column that no longer exists. So just tidy up after yourself. And again here, I would remove these two columns as well. So I hope you enjoyed this practice activity and I’ll see you in the next section where we’ll be transforming dates and.