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

150. Other M Functions

Now, you may have noticed when we were doing the custom column that there are rather a lot of functions, and you might be thinking is this time for me to go through absolutely all of these functions and tell you what they do?

The problem is, if I do that, you will drown in functions, and that’s no good. So, what I’ve done through this course is taken you through each of the more important functions using the home, transform, and add column functionalities as we’ve been going along. These are the more important functions.

However, they are far from a complete list. So how else can you find more about functions?

Well, I have provided you in the resources near the very beginning of this video two documents, one in PDF form and one in Word format. Look at the PDF form, which I’ve got here as a PowerPoint presentation. You may recognise some of these graphics that I’ve been using. You can see that we have date, date/time, date/time/zone, day, duration. List, number, table, time, and text. These are the prefixes to the functions. So, we have for instance Date.AddDays, Date.AddMonths. What I’ve done in this document is given you a list of all of the fairly important functions. There are categories beyond these 10, like XML and JSON and more esoteric stuff, but at this stage of learning about M, these are the most important ones.

Now, you notice that there are very similarities. Date, dot is in current. Day, month, quarter, week, year is in next n. In other words, you can provide the number, how many days or months or quarters afterwards. So, what I’ve done is I’ve gone through this reduced list and highlighted the really important ones. You may notice we’ve been doing quite a lot of these functions as well. So, we’ve got this as a particular set of functions that you can look at, so look at the highlighted ones first. Table, there’s an awful lot of them. You can really go into depth with the table functions. Then I’ve created two Word documents, in fact. We have a full list of all of the functions, and a reduced list.

Now, this reduced list is the one that I have shown you in the PowerPoint presentation, so you can see the ones that I’ve highlighted previously. So, what we’ve got is the syntax, a description of what it is. What this does, it returns a value where the number of days increased by, however, many days you want. I’ve given you a syntax, and I’ve given you an example. And when I say I’ve given you, this is actually from the official M documentation. Really all I’ve done is put it into a much more suitable, easy-to-use format. I have also put sometimes what the XL equivalent is. So, if you know that there is a particular function in XL that you want to use in M, why not just use this Word document and search for it?

Now, I’ve not done an exhaustive search. All of these XL equivalents are me. I’ve gone through, oh yes, this looks like XYZ. Especially in the number section and the text section, there’s a lot of those there. So, there is a reduced list, and then there is one that is not reduced which goes beyond all of those functions that I’ve just mentioned. My recommendation is start off with the functionality that you have learned in this course, and then expand as you need it. So, you may say, well, I want to have a look at all of the number functions and see what they do. Or you could say I want to have a look at all of those that I’ve highlighted in yellow. You could say I want to go right to the very beginning and work my way through. It’s not that long a read in the reduced version, 53 pages. It’s a bit longer in the non-reduced version. If you are doing the certification, the exam, then I would recommend sticking, at least initially, with the highlighted functions, with the ones that we’ve been doing throughout this course, and including the thing that we just looked at, the if, then, else. And of course if you’re doing the certification, practise makes perfect.

So, this is how you can find more information fairly easily, fairly, it reads fairly well, I think, as a document for functions in M. One thing I must stress, don’t forget the capitalization has to be correct, so use Date with a capital D, Day of Year with capitals D or Y. If you don’t, then you will not have a correct function. And of course, remember where it’s not capitalised. So, if, then, else is not capitalised. If you use the hashtag shared, that’s not capitalised, but the vast majority of functions are capitalised.

151. View and Help Menus, including Column Properties

Now, I know that we’ve got one thing left in the add column, the “invoke custom function.” But to use that properly, we’re going to have to have a look at the view menu first. So in this video, we’re going to have a look at the view and the help.

Now, first of all, these continually change. So, if your layout is not exactly the same as mine, don’t worry, it’s because every month Microsoft add a little bit more and a little bit more to Power Query. So, let’s see what we’ve got. First of all, in the layout, we’ve got the query settings, so that allows us to get the list of steps on the left hand side- on the right hand side. So, if it is not there, just go to view-layout.

Similarly, the formula bar, which is here, you may or may not want to see it. It’s generally a good idea for you to see both, to be honest. But, you may be short on space, or, you may want to see a huge number of columns. Just switch off the query settings just for a little while. Monospaced, it gets the font into courier, so each letter takes the same amount of space. Show white space, that allows you to see white space literally spaces which are white, not really often used. Quality of the column, so see if there are any errors in the column. Could be useful, as could the next one, the column distribution. How many unique values there are?

So, in this particular column, there are 12 distinct so that’s all of these, but none of these are unique, none of these regions only appear once. Whereas in house sales, there are 923 values which are unique. And similarly, the column profile, gives you this information per column. So, again, could be useful, but you have to highlight a column for that to happen. So, you can see how many times a particular value has been used. So, 7332 has been used three times, for instance. Go to column: that’s if you want to go quickly to a column which may be right on the other side of where you are or, maybe, you can’t find a column you’ve got so many columns, you need a list of them to scroll down. Parameters-always allow: not often used, that is something that was put in the June 2019 update, I think. No, I’ve just looked it up, it’s the July 2016, so it would be June 2016 update. Query dependencies: So, this says okay, we’ve got one particular query, is it reliant on another particular query?

So, if you go into any of these errors that we’ve previously looked at, and clicked on query dependencies, you can see that this particular query is dependent on another query, which is dependent on a source document. So, this could be useful to actually have in terms of auditing. When you have a query feeding into another query, feeding into another query, you remember the reference query, or duplicate query, that we had earlier. You can change the layout it could- you could go from left-to-right, or top-to-bottom. I wouldn’t recommend any of them too, they are a bit more confusing in my opinion. And you can zoom in and you can have full screen. Looking at the help, we have some guided learning documentation, training videos! Who on earth would do training videos about Power Query? And it sounds like a really good idea! But unfortunately, when you look at it, let’s go down to an example of Power BI desktop for instance. Sounds like a really good idea. Just waiting for it to load. So, here we have Getting Started with Power BI. That sounds like a brilliant topic. Why am I doing this course? I mean, we’ve got Microsoft’s official course here. Which is oh. Only 8 videos long. Oh, well, I’m sure Power BI desktop is much bigger. Six videos. Ah. So, unfortunately, not as good as it seems, however, I can recommend it later on, for the monthly updates. So, it is worth looking at, I mean, since here is the Power BI Desktop Update for December 2019 so you get up-to-date with all of the additional things that YouTube, uh, that Microsoft have put in. It’s also available as a blog, for instance.

So, this is what I looked up to get the when they allow, always allow parameters was in. When it came in, when it came in the June update, which was the first of July. And this is a text version, with little clips of videos, showing what is new in each month. So, it really does keep evolving and updating. We then have other help like documentation, support, and the community, so you can have a look at the Power BI blog and so forth. In the next video, we’ll just have a look at this one that we have omitted. The advanced editor.

152. Profile the Data

Now, before we do go on to the Advanced Editor, I just want to take a quick look at section two, profile the data, identify data anomalies, examine data structures, interrogate column properties and interrogate data statistics.

So, there’s lots of ways that you can identify data anomalies. You can do that individualization, for instance, with a scatter graph.

However, as we have just seen, we can use the View Data Preview section to actually do a fair bit of this.

So, identify data anomalies. So, if you have a look at the East Midlands and have a look at the column profile. So, I need to then click on the East Midlands, if you haven’t already. So, you can see those accounts are 374, zero errors, zero empty, but there are two zeros. And that leads me to thinking, “Okay, why are there two zeros?” Then we can see that seven, eight, eight, five is in three times, and I might be questioning, “Is that really appropriate?” Maybe, it’s just a coincidence or, maybe, something has gone wrong. And then we can have a look at the column distribution. We can see, well, there’s 38 distinct and 22 unique, which means there are 16 values, which are repeated. So, you can remove the duplicates, you can investigate it further.

We can have a look at whether there were any errors. So, we would see errors or empty. And for text we would also see like the min and the max, and we can see value distributions on the left hand side. So, it’s by using these two, column distribution and column profile that we can profile the data. We can also have look at the column quality as well, which gives us a bit of a summary. Have we got empty values? Have we got error values?

So, using these three tools, column quality, column distribution and column profile, we are able to profile the data, we’re able to identify data anomalies, we’re able to identify data structures, interrogate column properties. All with just a few check boxes. In the next video, we’ll have a look at the Advanced Editor in some detail.

• Category: Uncategorized