PL-300 – Section 18: Part 2 Level 6 – Add Columns, View and Help Menus Section

142. Column for Examples

Hello, and in this section, we’ll be looking at the add column functions.

Now, most of these functions, the from text, from number and from date and time are duplicates of the transform equivalent. So, we won’t be looking at those particularly. Instead we’ll be looking at the general. And we’ll start off with column from examples and it could be you have an example of something and you want the computer to work out what exactly are you trying to do? So, let’s create a new blank query. So, you can go down to blank query, or you can click on the lower part and go blank query there.

Now, this query is going to be a list of numbers from one to 20. So do you remember how to do that? Equals, curly bracket. The curly brackets denote a list, and notice that it’s ended the brackets as well. One, dot dot 20 and press enter. And now it’s list, we convert it to a table. So, here we have our column. And I want to have a new column which is two, four, six, eight. In other words, it’s double that.

Now, okay, I know what you’re thinking. You can go add column and you can go to number, standard, multiply and two, and that’s exactly right. But what if you couldn’t remember that’s how you do it, or you just wanted the computer to work it out for itself? Well, we can use the column from examples.

Now, there are two subdivisions here from all the columns and from selection. So selection just uses the column or columns that you’ve highlighted, and all column uses all of the available columns. You’ll see what I mean in a moment. So, we’ve only had one column, it doesn’t matter which one you use for this particular instance. And now it’s saying okay, this is your existing data. What is your new column that you want? So, I’m going to put in two. And you notice the computer, it has got some suggestions. It could be something to do with trigonometry. No, not in this instance.

Now, I’ll put in four, and press enter. And you can see what the computer’s done, it’s worked out that it is a multiplication. Because when I put in two, the next number could have been three, it could have been addition. In fact, let’s have a look at that. If I delete four, all of my examples are deleted, all of the continuations. But then I put three and it goes, ah you want to add one and not as I previously thought, multiply by two.

Now, in each instance, the computer is saying the formula that you think it wants. Now, let’s take a different example. Let’s say I wanted to divide by 10. Okay, I’m going to type point one, so dot one, full-stop one. Notice what happens. And I delete the second example because four is not a continuation. We start off okay, dot two, dot three, dot four all the way down to dot nine but then we have dot 10. So, what the computer has done is not divided this number by 10, it has text dot combined, it has combined together a dot from the text.

Now, the text is currently an ABC, 123 text column, so a general column, and it’s just converted it into text just to be on the safe side. But that’s not what I want. I want to divide by 10, so let’s try again. Instead of dot one I’ll put in zero dot one. And now the computers, hang on, ah, what we need is something very complicated. Nope, the computer hasn’t quite worked it out yet. So, I’ll put in another example, zero dot two. And now the computer is going, ah, it’s a divide by 10. So, the computer has its own ideas as to what it thinks you might want, but it’s not always the right answer. So please, please check that the end formula you want is exactly what you do want. So, I’ll click okay on this, and there we can see it has inserted a division. So table dot add column, and the previous step, converted to table. The new column is called division, and each row is going to be the column one divided by 10. There’s column one divided by 10 to create the division in a type number.

Now, let’s take a new query. So, I’m going to create a new blank query, and this I’m going to populate with dates. I’m going to populate with every date in the year 2022. Can you remember how to do that? We had to look at it in the last section. So equals list with a capital L dot dates with a capital D, open bracket, and then the hash or pound sign, and in lowercase, we need a date, open bracket, 2022 comma one comma one. That’s in Japanese format, year, month, date. Then how many repetitions we want? 365, and then the step. That is a duration, so hash or pound duration, open bracket, and the duration is in days, hours, minutes, seconds. So, here we have a list of the dates in 2022, and I will convert that into a table. So now I’m going to add a new column from this example. So, I’m just going to type in the number one.

Now, one could mean a lot of things. It could mean the day, it could mean the month, but you’ll see, it also could be a lot of stuff. It could be the quarter, it could be the week of the month, week of the year. And you can see the computer is actually giving us examples of what you can do with a date. So, it’s very easy to go, actually that one, that’s the one I want or this one over here. Here you can see the format where you have a year and then a time, a T, followed by the time in between. So suppose I wanted the day of the week, or the day of the month. I just click on whichever one I want and the computer then follows it through. So, there we can see 31st of January, 1st of February. Make a mistake, okay, just enter a new date, new number. And I’ll say I want day of the year, and again, the formula changes. And these should be fairly familiar formulas at least to recognise at this stage. So click okay, and there we have our day of the year.

Now, let’s just take a slightly more complicated example. We will open up our Power BI at the CDs spreadsheet. Now, CD spreadsheet, it’s got free heteros. First two we delete. So, we’re going to delete these first two. So, we remove the top two rows, and then we promote the third row here. So click on the use first row as headers for that. So, let’s see what we could do with a text file. So, let’s have a look at this folder path.

Now, this is when there is a major difference between use a column from examples from all columns or selection. If I was using from all columns, then you can see that it can get my suggestions from folder path, or from paths, or indeed from any other column. And here we can see lots of examples. Overwhelming really. Instead, if I now just use a column from examples and from selection, you can see the only column that is being used is this folder column. So nothing else works.

So, let’s say I wanted to start typing in V colon slash, music, slash. Press enter. So, I’m not taking its example, which is just a direct copy of the folder path. And you can see that the computer’s gone text before delimiter and it’s taken the letter A. Well, that might be what you want, but what you might also want is, I want all of the text before the second, up to the second backslash. So again, do be careful before just blindly accepting what it offers you. Make sure that the text, the formula, which as I say you should now be fairly familiar with, is actually telling you what it is.

Now, I’m going to take another example. I’m going to highlight these two, file extension and attributes. And I’m going to type in dot WMA, and then a capital A. And here the computer has worked out that we’re combining these two columns together. And similarly, if I do it the other way around, or if I add in a space, the computer does have a fair bit of intelligence, but only for the most common examples. So, here’s what you can do with this column for examples. You can have a reference to a specific column. So, you could have a reference to the file extension, and that includes trimming, cleaning, and case transformations. So, I could do dot WMA in capitals, and the computer will work out that’s text dot uppercase, or upper. We could have text transformations. And these are the various sort of transformations that we have here. We could have date transformations, and we could have number transformation. And we could also have time transformations as well.

Now, here’s a complete list, if you’re interested in everything that it can do. So, you can see the web site that I got this from. So text transformations can be combined with place, length, and extract, and remove end key characters. So, for instance, I could say I wanted dot WA.

Therefore, missing out, the M. So, computer goes, I’m not sure what you mean. Okay, I type it again for a second value and it’s going, ah, you’re probably replacing the letter M we’ve nothing. Equally, if I went to dot WXA, it’s saying, ah you’re replacing the M with an X, right. Got it. Looking at other examples for date transformations, you can see day, month, year, and various extractions of those combinations. And for time you could extract the hour, minute, and second. So, if we go to an example which has got time, so I’m going to use this date created. I’m going to add this to my examples just by checking this box at the top, and I’m going to uncheck these two. So now the computer just thinks dot WXA is a literal. Right, so, let’s delete my examples, and I’m going to type in 21. And the computer has worked out that’s the hour. And similarly if I type in 45, or 25, it works out that that is the second. And similarly, we also have numbers. So, we’ve already seen the fan example of numbers, but let’s suppose that we had this number, and we were going 34, 36, and the computer’s going, ah, it’s double that. And it can do quite a number of transformations. The major ones of course being multiply, divide, subtract, and add. It can even give it a go with having more than one field, more than one column. So, if I have this indent, which always has the number two selected, and I type in two more than the first column, 1920, then you’ll notice it has worked out, it is these two columns added together. So column from examples. This can be useful to add in another column when you know the answer that you want, but you’re not entirely sure how to get there. And you can select all the columns to help give the computer an idea of what you want or just a selection. And don’t forget, you can select more than one column by selecting the first, holding down control, and selecting a second and a third. Or if you want to arrange, holding down the first and then holding the Shift button, and clicking on others.

143. Conditional Column

In this video what we’re going to do is have a look at Pivot Table HPIRegions3, and we’ve got going across all of the regions and going down the dates. But there is a particular problem with this particular item, in that the layout is in compact form as opposed to tabular. If it was tabular you would have years in one column, quarters in another, and data in another. However, in compact form it’s all in one. How are we going to get the computer to separate out years from quarters, from months?

So, let’s add this quarry we get the data and this is Pivot Table number three, so there we go HPIRegions3. So, I’ve got some idea that this preview might be a bit old, simply because it’s taking it from a cache somewhere. So, I’m going to promote all of these, this first row up. So, I’ve used the first row as headers. So, almost everything else is trivially easy compared to what we’ve done. We just now needed to transform everything by un-pivoting the columns. So, I’ve highlighted this column, so I want to un-pivot the other columns and there we have whatever’s in the first column and then East of England, East Midlands, and the value. So that bit is the easy bit. That bit, not that easy in Excel but very easy in Power BI. So how do we separate this out into years, quarters, and months? And we do that using the Conditional Column that we’ve got up here. So, what I want is a column for years, column for quarters, column for months. So, if I click on Conditional Columns, it says, “Okay, this is your new column.” So my new column is going to be Year. And I’m going to call it, yeah, just Year. So, if the Row Labels. So, what separates the years out from being different from quarters and months?

Well, maybe, if they begin with, and you can say we’ve got equals, begins, ends with and contains, and then negation, the opposites. If it begins with one, then we want the year, then we want the row labels. Oh, but that’s going to actually give us a literal row labels. So, if just show you, there we have the row labels. That’s not what we want. So instead, what we need to do is press this abc123 button and say instead of this being a literal I want it to be a column. I want it to be a reference to a column. This where our column is called Row Labels. You can see that if this doesn’t happen, when none of these rules are met, then I want it to be literal null, which is not the string null, you can see that it is in italics. It is actually being translated as null, an absence of data. So, what we can do then is cascade all this down. So, we fill in the blanks. So, we go to transform, and fill down. So, now we have 1995, until we have the new value of 1996. So previously, we had 1995 and then a lot of nulls. We’ve now filled in all those with the value above. So that’s year. Let’s get in the quarter. So, the quarter starts with the letter Q. So, fairly easy now for us. So, this is the Quarter. If the row labels begin with the letter Q, then give us the column Row Labels. And we can fill that down as well if you so wish.

Now, I’m not going to do in this particular case, because I want eventually to exclude any of these quarter totals. So, it’s helpful if I know where they are exactly. And then the month. Okay, so the month is everything else. So, what I could do is say, well, actually the month is three characters long. So, if I can add a new column, and I extract the length, and then I can say where the, so this is the Month column. And I don’t recommend using Year and Months as column names but sometimes you might just have to for user interface purposes. It might just be easier for the end user to know this, but it could be Year of House Purchase, Month of House Purchase. So where the month is equals to three, then give me the Row Labels. And so that would work. Alternatively, you could say where we don’t have anything in the year, and we don’t have anything in the quarter, but we have filled down the year. So, let’s get rid of this filling down the year.

Now, it might make further steps break, but in this case, it won’t. It just gives you that warning. So, I don’t want to insert the text length, because we’re not going to use that for this. So, where the year is blank or where the year is not equal to anything, then give me nothing. This is the month and, so else if, so the next thing if the year is not blank, then give me nothing, if that’s not the case, then when the quarter is not blank, give me nothing, else, give me the column Row Labels. And we do that, click okay, and we get nothing.

So, why is it nothing? It’s because these are not actually blank, they are null. And there’s a difference between blank, which is a string of zero characters and null, which is absolutely nothing. So, let’s just modify that by clicking on the wheel and say where year does not equal null, and quarter does not equal null. And these dot-dot-dot (…)s, they can move up, move down, and delete the existing condition. And you can add additional clauses if you want to using Add Clause. So now, we can see we’ve got January, February, March, April, May, June, and so forth.

Now, what we can do is fill down the year, and it won’t affect the value of the month because the month is based on the year at this stage before we fill it down. So now what I’m going to do is I’m going to highlight these three new columns, and I’m going to go across and hold down Ctrl and highlight Row Labels, and I’m going to transform this by un-grouping, sort of like un-pivoting, other columns. So now we have original Row Labels, we have the year, we have the quarter, we have the month, and then we have what we get from an un-pivot, which is the attribute, so we’ll call that the region. And the value that is the number of house sales. So, we call that the region, and call this the house sales.

Now, you can see the very first items we’ve got are for the grand total of the year 1995. And then after that, we have quarter one, and then after that, we have January for 1995.

Now, generally, what you would want is no duplicates in your data. So, you wouldn’t want the total of 1995, and you wouldn’t want the quarters of 1995 because you can work that out from the monthly data. So, let’s get rid of that. How do you get rid of it? Well, you get rid of the year totals by, and in fact, you can get rid of the quarters with just one movement, which is to filter out where the month is blank. So, you could filter out where the quarter is null by saying, “I want all nulls on the quarter.” Without keeping the year totals, which may or may not what you want. So instead, what we’re going to do is filter out the month where the month is blank. I’m also going to remove the quarter column, not needed now, and I’m also going to remove the initial Row Labels column. So, I’m going to remove those. Neither of those are needed now.

Next, I’m going to combine the year and the month together. So, I’m going to merge these two columns. And I’ll merge them with a space in between and I’ll call this Month of House Sales. And so that’s replaced the two existing columns with a new one. If I’d added a column, that would then have three columns, now got the one. And now, I’m going to convert that into an actual date by going to transform, date, past. And so now we’ve got first of January, second of January and so forth.

So, that is how we can tackle a pivot table, which is in compact form with various hierarchal levels, we can use the conditional column, if we can identify how they’ll split. If we can find out if all of these are heading ones, all of these are heading twos, all of these are heading threes. We separated out into different columns, which, had the data been different, it would already have given us. And then we un-pivoted and we merged together the year and the month, after having removed the quarter totals and the year totals.

• Category: Uncategorized