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

144. Resolving Errors from Conditional Columns

So, what we can do now is we can create any sort of visualisation but I just received a dialogue 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, can’t provide 1999_2000 as a DateTimeZone. 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 earlier stages. And I think I can get to it in the preview because the top 1,000 rows. Here we’ve got an error at 721 which is what the other query showed us. So, why’s 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 conditional column, what we said was if it starts with a one, then give us the Row Labels. What if it started with a two? What if it was in the year 2000?

So, we need to go back to this early step, click and add to close. Row Labels, begins with two and then give us Row 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’ll be additional work that we’d need to do if we wanted to really use this because for instance, we’ve got Inner London and Outer 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 and just by altering this dialogue box again and deleting this item and close and apply, if you do find that there is an error, there 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 row number but then you have to work out what on Earth is the error with the row number? And then we’ll need to go down to row number 721 and try and first of all work out where is row 721.

So, for error checking, we could do with an extra column. A row number column and we’ll do that in the next video.

145. Index Column and Duplicate Column

Now, in the previous video we saw that there was an error on row 721. But we may be 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 row 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 and down in anything you want so I’ll suppose you wanted it starting at 10 and going up in two’s, absolutely fine. So, let’s add an index column to this so starting from row one and so here we have our row one, two, three, four, five et cetera and I’m going to call it Row Number and clause and apply.

So, now we can more easily identify where row 700 and I think it was 31, 21 and you notice that the row numbers in this visualisation in this looking at the data isn’t actually in row order. So, it’s a good job we got this row number. So, we’re looking at row 721 but we can’t actually express the query because it tries to add a column called row number which of course we’re now using so you can see why row number is not the best column name so I’ll call that RowNumber without a space so like to view the errors so we can see row number 733, 734. They’re fine actually, we’re looking at the errors so there’s error 721, 722 so we can go down and find or indeed, we can 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 row number to most non-complex, 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.DuplicateColumn and this is the column that you are duplicating and this is the new column name.

So, that’s a couple of more general functions, index column and duplicate column. In the next video, we’ll have a look at custom column and that is what you can really spend a huge number of hours on. We won’t spend huge number of hours on. We’ll just go over the basics of creating your own custom columns.

146. Custom Column – If Then Else

Now, we ended the last video with this month of house sales and I had the comment that London is duplicated, because we have Inner London and Outer London, and also England is duplicated. So, what we could do is just filter them out, however, what if you actually needed those rows to remain but the month of, or the house 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 code essentially, up here, except it’s in a dialogue box. So, we’re adding a new, blank column, and then we get to write in the code. 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 depleting 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, auto complete giving you the various syntaxes. But what I want, in this particular case is, if the region is London, then give me 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 auto complete. So, there’s a squiggly underline, 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 auto complete by whatever you’ve got highlighted, just press tab, and it auto completes. So, if the region is equal to, and in double quotation marks, so London, then, null.

Now, notice, the computer’s 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’s else space if, so else 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 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 lowercase, no uppercase capitalization whatsoever. Null is also lowercase. Also notice that unlike some languages, I haven’t to end it, I don’t need an end. Also notice the 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, let’s, I’m going to copy this, I’m going to copy the text for a particular a reason, and click okay, 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 it’s not how the computer’s 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 code that we’ve just written and when we go in 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 without totals, 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, ors, ands, nots, you can use greater than, less than, not equal to signs. And notice that the not equal to sign is a lesser than or greater than so it’s not anything more esoteric that you may use in other versions. And notice that the equals sign, is not two equals signs, again if you’re coming from a language like C#, it’s one equals 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 okay. And here we have the house sales without a total, and because we’ve used an or, because we have used Boolean logic when I click on the wheel, we get back to this Custom Column, dialogue 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 C# 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 okay, click close and apply, we’ll go back to, where we were previously. (dialogue box dinging) Just loading some things. We still have the same error from before because I went back and re-corrected 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’ll change that to, one, two, three, a whole number, close and apply. Change that now to a sum. And here we have all of the data. London and England, the rows are still there but they’re not being shown because they are in this particular visualisation 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 visualisation. And we’ve done that using if, then, else. The else is optional, but I do recommend you always put in an else because the computer needs to know, what is the answer if it isn’t this, it needs an alternative.

147. Converting Text from a Different Locale to a Number

Now, in this video, we’re going to create another custom column, and this is going to answer the question what happens if I get data from another locale with regard to numbers?

So, we have previously had a look at what happens if I need to convert with different locales for dates. And that used the culture argument.

Now, if you go into the Power BI manual that you can download near the beginning of this course, you’ll see that there are some 34 different functions that allow an optional culture with nullable text. So, for instance, Text.Lower, Proper and Upper. It might be that the culture will therefore impact on what is a lower or an upper character.

Now, there are 34 of these functions but I think outside of the dates ones that we’ve had a look at previously, the one you’ll probably be using the most is Number.From and Number.FromText. So, this returns a number value for a text value and has an optional culture as nullable text. So, what I’m going to do is I’m going to open up a CSV file that we have used in the past. So, this is our March.csv file. Very small. And what we did in the past was take the pound sign out, so I replaced values and then I converted it into a number, so we’ve got seven pounds 99 pence. But what if our source data doesn’t choose the dot as the decimal separator? What if it used a comma?

So, I’m just going to simulate this. So instead of a dot, we need a comma. So, now if I try to change this into a decimal number, 7,99 becomes 799. So, the computer thinks well, you’re using a comma in your locale as a thousand separator, and therefore I can safely just ignore it. That’s not what we can do here because it is seven and 99/100. So, what I need to do is add a column and I’m adding a custom column. So, this column is going to be outAsNumber. And what I’m going to do is use the function Number.FromText. So that one there. And we’re going to take the Out as your source data, and that’s fine, that would actually convert it as before unfortunately. But if I add a locale, which is an optional second argument, so the locale in this case, let’s say this is going to be the Spanish of Spain. Click OK. Now, you can see the computer’s correctly interpreted the comma as the decimal separator.

So, if you have things like numbers that are from a different locale, you have two options. Either you convert the comma into a dot or the dot into the comma or you can use a culture-aware function, such as Number.FromText to identify what we’ve got in a different culture. And that can be done all using add column, custom column.

• Category: Uncategorized