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

1. Column from examples

Hello. And in this section we’ll be looking at the add column functions. Now, most of these functions 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 start off with column from examples. And it could be you have 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 list of numbers from one to 20. So do remember how to do that. Equals curly bracket. The curly brackets denote a list and notice that it’s ended brackets as well.

One dot 20 and press Enter. And now it’s a list. We convert it to a table. So here we have our column and I want to have a new column which is 2468. 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. Or we can use the column from examples. Now, there are two subdivisions here from all 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 only have 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 has done. It’s worked out that this 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 ten. Okay, I’m going to type zero, one, so one false dot, one. Notice what happens and I delete the second example because four is not a continuation. We start off OK, two, three, four, all the way down to nine. But then we have ten. So what the computer has done is not divided this number by ten. It has text dot combined. It has combined together a dot from the text. Now, the text is currently an ABC one to three 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 ten. So let’s try again. Instead of one, I’ll put in zero one. And now the computer’s eyeing on. Ah, what we need is something very complicated. Not the computer hasn’t quite worked it out yet, so I’ll put in enough example zero two. And now the computer’s going, Ah, it’s a divide by ten.

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 N formula you want is exactly what you do want. So click OK on this. And there we can see it has inserted a division. So table, 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 ten. There’s column one divided by ten to correct 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 a look at it in the last section. So equals list with a capital L, date with a capital D, open bracket and then the hash or pound sign.

And in lower case, 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’ll convert that into a table. So now I’m going to add a new column from this example. So I’m just going to just 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 31 January, 1 February, make a mistake. Okay, just enter 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 recognize at this stage. So click OK. 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 this CDs spreadsheet.

Now CD spreadsheet, it’s got three header rows. First two we delete. So we’re going to delete these first two. So you move the top two rows and then reproduce the third row here. So click on use first rows header to 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 for 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 path or indeed from any of a column. I mean 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 colonmusic 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 has gone text before the limiter and it’s taken the letter A. What that might be what you want, but what you might also want is I want all of the text before the second or 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 are 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 is 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 dot WMA in capitals and the computer will work out that’s text 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 is a complete list if you are interested in everything that it can do. So you can see the web site that I got this from. So tech transformations can be combined, replace, length and extract and remove and keep characters. So for instance, I could say I wanted WA, therefore missing out the M. So computer goes, I’m not sure what you mean.

Okay, I typed again for a second value and it’s going you’re probably replacing the letter M with nothing. Equally, if I went to WXA, it’s saying, are you 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 and 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 W XA 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 simply 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 a fair example of numbers. But let’s suppose that we had this number and we were going 34, 36.

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 into 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 Offer.

2. Conditional Column

In this video. What we’re going to do is have a look at pivot table HPI regions three. 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 tabula. If it was tabular, you’d have years in one column, quartered in another and dated 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 query. We get the data and this is pivot table number three. So there we go. HPR regions free. So I’ve got an idea that this preview might be a bit old simply because it’s taken it from a cache somewhere. So I’m going to promote all of these, this first row up.

So I use first row as headers. So almost everything else is trivially easy compared to what we’ve done. We just now need to transform everything by unpivoting the columns. So I’ve highlighted this column. So I want to unpivot the other columns and there we have whatsoever 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 levels so what separates the years out from being different from quarters and months? Well, maybe if they begin with and you can see we’ve got equals, begins, ends with and contains and their negation the opposite. If it begins with one, then we want the role labels.

But that’s going to actually give us a literal role labels. So if I just show you there we have the role labels, that’s not what we want. So instead what we need to do is press this ABC one two three 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 column is called rule levels. You can see that if this doesn’t happen when none of these rules are met, then I want it to be the literal null, which is not the string null. You can see that it is in italics. It has actually been translated as null, an absence of data. So what we can do then is cascade all of 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 nodes. We’ve now filled in all of those nodes with the value above, so that’s a 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 levels begins with the letter Q, then give us the column raw levels 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 easy for the end user to know this, but it could be year of house purchase, month of house purchase. So when the month is equal to three then give me the roll levels 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 a 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 lens 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 they 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 levels and we do that, click OK 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 doctor dots, they can move up, move down and delete the existing condition. And you can add additional clauses if you want to using add a 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, control and highlight roll levels. And I’m going to transform this by ungrouping so unpivoting other columns. So now we have our original roll levels, we have the year, we have the quarter, we have the month, and then we have what we get from an unpivot, which is the attribute. So we’ll call that the region and the value, that is the number of house sales. So 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, but I’ll keep in the year totals, which may or may not be 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 raw labels column, so I’m going to remove those, neither 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 that’s replaced the two existing columns with a new one. If I had 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 pass. And so now we’ve got 1 January, 2 January and so forth. So that is how we can tackle a pivot table, which is in compact form with various hierarchical levels. We can use the conditional column if we can identify how they are split, if we can find out all this, 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 unpivoted and we merged together the year and the month after having removed the quarter totals and.

3. Resolving Errors from Conditional Columns

So what we can do now is we can create any sort of visualization. But I just received a dialog box saying that there are errors. Let’s have a look at the errors. And you’re looking at these errors and you’re going oh my goodness, why are these errors? And you simply don’t know. So I can’t provide 90, 99, 2000 as a date time zone. It’s giving us some clues as to what’s happening 1999, 2003, but this is when you actually need to go down into the data and error check. And the problem when you do that is back to one of our very earliest stages. And I think I can get to it in the preview because it’s the top 1000 rows here, we’ve got an error. So at 721, which is what the other query showed us, so why is there an error at 721? It’s because, and I’ll just scroll down to 721, we go into the year 2000.

And when we got down to creating all of these items, the additional column, conditional column, what we said was if it starts with a one, then give us the raw levels. What if it started with a two? What if it was in the year 2000? So we need to go back to this earlier step, click and add to close raw labels, begins with two, then give us raw labels again. And now all the rest will work. So if I close and apply and it’s just creating it all, it doesn’t give us any errors. And now I can create, let’s say, an area chart. And in this area chart we’ll have total of house sales as the volume and the month in the axis and the region in the legend and you can really see where the total really dropped off in 2008 and we can drill down and do everything else.

In fact, there will be additional work that we would need to do if we wanted to really use this. Because for instance, we got inner London and out of London and London. But basically if you are doing more advanced stuff like all of this conditional formatting and you then combine things together my advice is before you get to that stage so before we get to the merge column stage, actually have a look at the data and see if there are any errors, see if it makes sense.

Because you never know what you don’t know. If something has gone amiss, then that is the stage when you would find oh my goodness, there is an error. So if you do get such an error and I’ll just recreate this error, but just by altering this dialog box again and deleting this item and clause and apply if you do find that there is an error. Here we go. One of the loaded queries contains errors. You can view the errors and it’s actually viewed in the Power Query Editor, which might not be right at the top, you get the raw number. But then you have to work out what on earth is the error with the raw number. And then we’ll need to go down to row number 721 and try to, first of all, work out where is row 721. So for error checking, we could do with an extra column, a raw number column, and we’ll do that in the next video.