PL-300 – Section 19: Part 2 Level 7 – View and Help Menus and Advanced Functionality Part 2

153. Advanced Editor

In this video, we’re going to have a look at view Advanced Editor. And what it does is actually shows us the entirety of the steps that we have done in a particular query. So, let’s have a look at what we have.

And first of all, we have the name at top. So, this is the name of our query. And then after line one, we have every single particular step on a different line. So, you can see line four, we have the third step, Promoted Headers. And here we have the name at the front and then the function.

Now, you’ll recognise a fair number of these functions like for instance, we just use the each or we use if Region equals London or generally equals England then nil and it’s put the each at the front because it’s saying for each row. So, there’s a lot of complexity in here. And you don’t really need to know too much about the complexity to be honest. But it’s not well presented because you can see all of these very long columns.

So, thankfully, we’ve got the Display Options. So, first display option, Display line numbers. I’ve pre-ticked this. You can probably have all checked. You may not have it in your version already checked so if you check it, I find that’s quite useful especially when you go to Enable word wrap. So, what word wrap does is stops the text from flowing off the end. So, now you can see line five actually occupies, or step five actually occupies four lines. And we can actually read them. It’s a lot harder to read I think without the line numbers, albeit that they are indented.

Now, for those of you coming from other programming languages, you might be interested to know that these are actually the names of variables. So, they are shown as steps but it says let this variable be equal to this. That’s why we’ve got the word let at the beginning. In very old programming language, and I’m talking here 1970s and 1980s, it used to be if you wanted to assign a figure, a value to a variable, you had to use the word let, let a equals one.

Nowadays, let is so obsolete, it’s not funny. So, a equals one is used. But in this particular programming language, we have at the beginning let and here are all of the variables.

Now, don’t worry, if none of this makes any sense to you. This is really advanced and at this stage, you don’t need to know that much about it. Later on, we have in. What this in does is tells us this is what we display at the end. So, we have our final step being Changed Type2 and here we have the in at the end.

Now, be careful. Anything you do in this Advanced Editor does get reflected into this Applied Steps. So, you can see now it says Changed Type3 because I’ve just edited steps or lines 20 and 22 accordingly.

Now, I just want to simplify this down because that was a lot of text and just create a blank query. So New Query, Blank Query and have a look at it in the Advanced Editor. And see what our steps are. So, we don’t have a Source, so let, so assign the following two variables. Source, that’s the name of a variable equals blank in and here’s your output, Source. And then everything you do in the Applied Steps gets built into the Advanced Editor.

So, it’s not something that you need to use as part of your day-to-day job. However, we are going to use it in the next video when we’re going to be talking about functions.

154. Functions and Parameters

Now, a few days ago, we wrote a custom column that said, if the region is equals to London, or the region equals England, then give me a null, otherwise would give me the House Sales. And the reason for that is because we had some duplication. For London, we had inner London and outer London as a separate role. And for England, well, the entirety of the data is England. And we wanted to not remove the roles, but just remove the number of House Sales.

Now, let’s suppose later on, we also have another subtotal in there. We have a subtotal of the North of England. So, then we can go back into this and expand this and say, well, Region equals North of England, for instance. That’s fine, easy to do.

But the problem is, what if I have done this in multiple places? So, I’ve done this in one query, I’ve done this in another query, is there’s a way to have a new function that says, give me the RegionHouseSales. And here’s the region. And here’s a number of House Sales. Why might you want to do this? Well, firstly, this looks a lot easier than this. So, you can give it to somebody else and say, Well, if you call this particular function, you will get the result following this logic.

Now, this is very simple logic, obviously. I don’t recommend creating get function based on very simple logic like this. But suppose you had a huge query, and it needn’t just be based on one simple custom column formula. Maybe, it’s based on an entire query, is entirely possible. I’m not going to get into the huge details of how to convert entire queries into functions. In this particular example all I want to do is something a lot simpler.

I want to convert this into a function. So, we’ve got the ease of use. So, somebody can have RegionHouseSales and supply the values. And that’s it. We also have reusability. So, we have one central place for your function. And if you need to change the functions, you can do so in one place, and use it again and again. And you can export it into another Power Query, set of queries and so on. So, what I’m going to do, is I’m going to copy this. And I’m going to create a new query. In fact, we did that in the previous video. But if you want to create a new query, just new query blank query.
Now, if you look on the internet about how to start an M custom function, you’ll see lots of ways. One way for instance, is to right and click on this and go to create function, which sounds like it should be an easy start, but actually, for me, adds complexity. What I’m going to start with is this query and this Advanced Editor. So, we have the very simplest Advanced Editor window, we’ve got. Let source in source. That’s all we’ve got. Right, so what we’re going to do, is first of all, I’m going to paste in the code that we have previously got. So, I’m just going to paste it in line three. And that’s it. I’m going to make an alteration to it later, but a very subtle one, I think.

Now, at the beginning, we have to name the output. But we’re going to go. And I’m going to call this RegionSales.

Now, I would strongly advise you not to use any spaces in any of these functions. It causes additional complexity. So, region sales equals and in brackets. So, these are soft bracket, I’m going to put my arguments. So, I’m going to put my variables that I need to manipulate. And we’ve got a Region and Housesales. So, I’m going to do that Region, comma, HouseSales.
Now, notice I’ve put HouseSales as one word rather than two.

So, what I’m going to do is simplify this, I’m going to get rid of all the brackets. I’m going to get rid of the space in HouseSales. What then follows, space and then an equals and a greater than sign. So, it says, and then. So, we’ve used RegionSales at the beginning, that’s our variable and the output we want is going to be, well, we’ve assigned all of this to RegionSales. So, the output we want is, RegionSales. So that’s what the output is. So that’s what in is going to be. And then what we need to do is nothing. That’s it. So, you can see all I’ve added really is a new line two. So, lines three and four, they are our function. Line six is our output. I just changed the name, and added into number two, line two. This is our input and our output variable. And these are our parameters. These are our arguments, and then so click done. Notice we’ve got an ABC query one on the left hand side. That now changes to an FX function.

Now, the function is still called query one. So, I’m going to change this function, I’m going to call it RegionSales. So, now we have a function called RegionSales and we have the ability to test this. So, I’m going to put East Midlands and the number of HouseSales two, three, four. And I’m going to invoke. I’m going to run this. And you can see the answer is two, three, four. That’s fine. We want to keep the number of HouseSales for the East Midlands. And notice at the top, what we have, RegionSales, open bracket, name of region, comma and the number of HouseSales. So, I’m going to delete that. This is an invoked function. And just go back to the actual function. And I’m going to say now, England, two, three, four and invoke. And you can see the answer is null. Which again, is exactly what we want. So, let’s delete this invoked function. And we’ll go back to our original query and go to the add custom, which as reminder, looks like this. And we’re going to change this to RegionSales. And you can see the autocomplete is working. Open bracket, and our arguments are going to be, Region and House Sales, with a space because that is the data we are passing in. All the rest of it can go.

So, the question is, which looks more complicated? That or that. Well, let’s see if it actually works. Click OK. And here you can see exactly the same where we have England. We have a null but we have London, we have a null.

Now, I’m just going to delete that again. And instead of going to custom column, I’m going to invoke a custom function. This is a bit more user friendly. So, what’s the new column going to be? Well, it’s going to be House Sales Excluding Total. And the function query we were using is RegionSales. And then what’s the Region variable? Well, it’s Region. And then what’s the HouseSales, and at the moment is expecting a literal. I’m going to change that to a column name. And it’s going to be the House Sales. Click OK. And there we go. So, this is our custom function. More user friendly, but still giving us the same answer.

Now, I think I have changed the name of this particular field. So, when I click Close & Apply and go to this, here it says Without Totals and now saying Excluding Totals, so let’s just drag this back in and change it to a. Some, again, go back in and change the type the output to a number. Whole number. And there is our output again. So, it does add an additional layer of complexity in the sense that it adds an additional query.

However, it removes complexity, because now whenever you’re using this function, you can do it in a very straightforward format. And if you find the function has change, well you can edit the function, rather than having to go through each query and find out why you’ve used it, and alter the code there.

Now, I’m just going to delete this function. So delete and can’t. It’s being used by our table that we were using our query, but then all these error queries also reference this query. So, you’ll find that we can’t accidentally delete a function as well. And just for fun, you can go to the View and Query Dependencies. And you can see that the function RegionSales is now a dependent. This query is now dependent on RegionSales. So, it’s now predecessor. It’s now something on which our queries rely. So, the easiest way, my particular opinion to create a fairly straightforward function, is to create a blank query, go to the Advanced Editor, copy your code into line three, and then in line two set up your parameters. So, if we go back to this function, we just had an equal sign and your parameters in brackets and then an equal and greater than sign which is and then and make sure that your input variable and your output variables match.

Now, creating functions can get really, really complicated if you need that level of complexity. A function can return, not just a single value, but a table, it can return a list. You remember what we had a look at the Advanced Editor, I said each of these were variables. And this one returns the entire table. So, your functions can return entire tables as well. But while you can go very complex, you don’t necessarily need to, if you don’t wish to. At least, start off simple. Use functions when you need to.

155. DateTimeZone Date Type and Functions

In this video, I just want to touch on the date time zone datatype.

Now, time zones, it varies across the world what time you’re in. That when you’re 12 o’clock in Great Britain or in the Canary Islands or in Portugal, then it will be seven o’clock in the morning in the eastern seaboard of America. Four o’clock on the western seaboard, and various times in China, all over the place. And you can see at the bottom that time zones are expressed in largely hourly units. So, one hour ahead of Greenwich mean time, which nowadays is often called coordinated universal time, UTC. So, one hour ahead would be western Europe. One hour behind, there are bound to be some places. I’m not sure what is one hour behind. But we know what five hours behind are, the eastern seaboard of the United States.

Now, it’s not true that all of the countries are four hours ahead, five hours ahead, six hours ahead. India, for instance, I think, is not a whole number of hours ahead. I think it’s at five and a half hours ahead? And North Korea, for instance, I think, has been varying between seven and a half and eight hours. But those are more the exceptions. But when you have any exceptions, then what you do to define a time zone has to take account of these exceptions. So, let’s get data from Excel, a power B I data, and we’re going to look at the computer login.

Now, we’ve had a look at earlier, and we have these time zones. So, Nora logged in at 1:15 p.m., but in the central time zone of America, six hours behind. And logged out again in the same time zone. So, this at the moment is a time, a date time. This time zone is text. And what you can do is combine the two. So, if we go into transform and merge columns with a space in between. Click okay. We now have one single column, albeit a text column, that shows the date and time. But we can change that from a text column to a date, time, time zone column. And you notice that when we do that the date and time functionality are usable. And so you can do the same with this one. Merge them together. And change it into date, time, time zone. So, there is your login and logout.

Now, there are a number of additional functions that you can use with date time zone, and the resources near the beginning of this course show you some of the more important functions. So, for instance, you can remove the time zone. You can change it into another time zone. You can change it to your local time zone. You can change it to the coordinated universal time or GMT or UTC. And you can get the time now, and you can extract the zones. So, you can see there’s not a huge amount additional you can do with the time zones, but it’s important that you know that you can have a single field, single column that contains not only the date and time but also the time zone.

So, that can be quite useful for when you receive international data. So, date time zone, not a huge amount in it. I can, obviously, subtract one from the other. So, if I go to add column, time, subtract, I get the difference in a duration. So, time zone’s now irrelevant because we’ve now deducted one’s point and time from another point and time. So, if you do have international data which includes time zone references, especially aware they’re not all in the same time zone. Say you’ve got Pacific as well as eastern time in America, or you’re in Australia and you’ve got times from the west coast compared to the east coast, then you can have them in just one easy-to-use field. And it makes calculations like subtractions a lot easier.

• Category: Uncategorized