PL-300 – Section 12: Part 2 Get and Transform Data: Level 1 – Home Part 2

101. Introducing the M Language

In the last video, we had a look at the fact that there were formulas in the formula bars. But these are very different types of formulas than are available in Excel. You’ll notice that each of these formulas are in two parts. There is a word, and then a dot, a full stop or period, and then another word. And you’ll also notice that there are some words which are capitals. So, what is all of this? This is called the M language. M for Mashup. And what this is for is to translate each of your steps into different formulas.

So, you can see here we have a formula which has Excel.Workbook(File.Contents(“ and in there is the name of the workbook. So, it might look a bit alien at first, but if you can see it’s going to do something to an Excel workbook, and it’s going to do something to the file contents and there’s the file. Then it starts looking a bit less strange. So, we’ve got Excel., file., Source, Table., and Table. In fact, the vast majority of the M language functions are this two-word naming.

Now, if I just Google “Power BI M language”, you can see for me the top item is from Microsoft.com. If you go into there, you will see that there is an awful lot of detail. Get information for over the 700 M functions. I click that, and here we have all of the different functions. So, you can see all of these words are the first part of the functions. So, we’ve had Table. before, these are all of the Table functions. And you can see that there are an awful lot of them. It would take you ages to read all of this.

Now, you can read all of this by clicking “Download PDF.” So, if I do that, you’ll see that it downloads an 800-page, very detailed document all about the M language, and if you’re all going to be some sort of M guru, then you might want to have read this just once. However, there’s an awful lot of functions, and for this course, we don’t need to concentrate on even half of these functions. So these are the functions that we’ll be using as part of this course. Date, DateTime, DateTimeZone, and Time, Day, that just gives a list of the days from Friday to Thursday, Duration, List, Number, Table, and Text.

Now, within this, and this is available as a PDF in the resources part of this course, just go right back to the beginning of the course. You’ll see a lecture called resources is attached to this. I have listed all of the main functions.

Now, you’ll notice there’s a lot of repetition. For instance, Date is in the CurrentDay, it’s in CurrentMonth, it’s in CurrentQuarter, it’s in NextDay, it’s in PreviousDay. So, what I’ve done instead is I’ve given you the important ones, but then I’ve highlighted the really important ones.

Now, if you are going forward to the certification, then my advice will be to memorise the ones with the yellow highlight. So, here are all the Day functions or Date functions. DateTime functions, there are only a couple that I really suggest you memorise. From and To are very important functions, but they’re there for all these different functions, so I’m not highlighting them on each page. At the least for the certification, you should be acquainted. You should know that these functions exist. DateTimeZone, that’s just giving you the time now, and so forth. These are the Day values. Durations. Json and Xml functions I’m putting in there because we will be, towards the end of this part, opening a Json and Xml document, but you don’t really need to know about these too detailed. Here are the List functions, and we’ve got things like the Min, the Max, the Sum, the Count, NonNullCount. Here are your number functions. A lot of these you can identify from Excel. Mod, Power, Round, Sign, Abs for instance. The Table functions, as you might expect with a table of data, are very numerous, but when you look at it, there’s only about twenty functions that I’m saying are really important, and a lot of those, like FirstN and LastN, can be paired together. Text functions. If you think of the Text functions in Excel, then these are they in M.

Now, in case you’re wondering, M is based on another language. You may have heard languages like Visual Basic, VB, or C#. Well, this is based on a language called F#. It is a four-tran language, which I think next to nobody uses. I may be a bit prejudiced about this, but C# and Visual Basic are the main languages. So, why they decided to invent a new language based on a language that very few people use, I don’t know. And then finally there are Time functions.

Now, there is something to be said about M functions in that they are case-sensitive, so you must have each word starting with a capital letter. So, you can see Text.PositionOfAny. You can see all of the capitalizations. Thankfully, the capitalizations are regular. So, if you know the command is ReverseRows, then you know that both of those have got a capital R at the beginning.

Now, another good thing about all of this with the M language is that everything that we’ve got in the Home, Transform, and Add Column will be translated into M. So that’s why I’m saying, if you’re going to use this for basic or intermediate, you don’t really need to learn anything about M. The computer will interpret it for you. However, there are some really good things later on if you do learn the M. As we are going through this course, I will be mentioning each of the M formulas because as I said, I think it’s important if you have even so much as a nodding acquaintance, that’s a good way to start learning.

Now, I have created a Word document which contains all of those 860-odd pages in a lot more readable fashion. It doesn’t contain all of the examples. It doesn’t contain all of the detail. But what it does contain is a lot fewer pages. Only 86 pages according to this. But I still think that’s actually a fair amount to read. Quite a bit. So, what I’ve done is I’ve created another manual which is called “Power BI Manual Reduced,” and what this does is it looks at those important functions that I was looking at earlier. So rather than wading through them all, you’re now wading through a select example. We have got examples here, we’ve got the syntax, we’ve got in words what it actually does, and it does this fairly swiftly.

Now, if you want the full thing of anything, say you wanted Date.WeekOrMonth, then please go back to your original reference. Remember, I’ve just Googled “Power BI M Language,” we’ll look at Date, we’ll look at WeekOfMonth, and that gives you more information about it. So, what I’ve done there is just given you a precep, but if you want more information, then go onto the Internet. That’s what it’s there for. But what you’ve got in these documents is enough to get grips with the majority of the commands. And sometimes I’ve even put down the Excel equivalents. So Date.WeekOfYear is the equivalent of WEEKNUM. In the next video, we’re going to actually start looking at the menus. We’re going to go roughly from left to right, starting from Home and then going to Transform and Add Columns, and we’re going to tackle at each stage a problem. So, we’re going to demonstrate what these functions are for and why you should use them and when you should use them. For instance, we’re going to start off with this problem: the column headings are nothing like what they should be.

102. Let’s Start Look at the Home Tab

All right, in this video we’ll be looking at the first half of the Home tab. The first bit fairly quickly to be honest. So, in the home tab, we have Close and Apply. Sp that Closes the Power Query Editor and updates the Power BI desktop with all of the new data. We have Apply which doesn’t Close the Power Query Editor but does Apply it to the desktop. And we have Close, that just goes straight to the Power BI desktop. New Source, this allows you to get more datas to add additional queries. Recent Source, these are the data sets that you have previously used. And Enter Data, if you just want to enter some data straight forward, just start typing. So, here is my data and I want to add an extra column while my data to. So, if you actually do want to create a new query source we’ve just entered data very infrequently. You have to do this, that’s how, and then click OK, and it appears in a new query over here on the left hand side. So, I don’t want that, so what I’m going to do is I’m going to go over here to the query section and I’m going to delete. So that removes the query from my Power Query Editor.

Now, there are two more options in the Manage Query that I’d like to talk about. Duplicate and Reference.

Now, you remember I said we had all of these steps. Duplicate gives me an exact equivalent with all of these steps. So that means that now if I add a new step here, let’s just change this data type. No particular reason. I that new step, it’s going to give me lots of errors it doesn’t matter.

Now, this first query has got five steps, this second query has only got four. So they are independent queries. However, Reference just says, “Well give me that previous queries results.” So, if I give you a Reference of this, then you can see the applied steps is give me a source of CDs. So, it just gives me the CDs. So, if I now add this additional step to my CDs query. Then we have this additional step having already taken place in our new query. So all it is is a reference to the previous query as opposed to let’s duplicate it, let’s give you all of the steps and then you can change either as you want. So which ever you want to do is perfectly fine by me. If you do want to have a second version of the same query the question is if I make any changes to the first query do I want it to apply to the second query? We’ve also got Refresh, so that will refresh one query or refresh every thing. Properties just give me opportunities to type in a description. There’s a couple other tick boxes here, including report to refresh just means it is refreshed when you click on the refresh button. And enable load to report means that it will actually, some of the data will come into here. That’s all they means, nothing major. Advanced Editor, that gives all of this all of this M-code we’re not going to touch that if at all, I think we will later on when we talk about custom formulas. But let’s steer clear of that to be honest. We’ll have a look later at Parameters and the Data source settings, this is more useful when you’re looking at Esquire Server, where you have to put in a password.

Now, so much for the problem with Excel. So that is the first half of the Home tool bar, going from Close to Query. Next video, we’ll have a look at Manage Columns, Reduce Rows, and Salt.

103. Home Menu – Manage Columns

In a sense, the manage columns and reduce rows does one and the same thing. It allows you to reduce the number of columns that you’ve got, or reduce the number of rows that you’ve got. So choosing columns, there are two options within this. Choose columns and go to column. Go to column just highlights a particular column. It doesn’t apply and additional step. It’s just like me clicking on a column at the top.

Now, if you want to highlight more than one column, you can do that by clicking on a column and then going to another column and holding down the Shift button on your keyboard, and it will highlight all of that as a range. So, I’ve highlighted column two, column three and column four, by clicking on column two, holding down Shift and clicking on column four. Alternatively, you can highlight disparate columns, in other words not just a range but this column, and this column, and this column, by clicking on one column and then holding down control, and then clicking on any other columns. So none of these column selections that we’ve just had a look at actually are a step. It’s only when you do something to those columns that they become steps.

Now, choose columns is saying what columns do you want to keep? So, let’s say I wanted to keep only the first two columns. Well I can un-select all of the columns, and then select the first two columns. Click okay, and that’s all I get, these first two columns.

Now, you’ll notice that the formula at the top is table dot select columns.

Now, this is in two parts. The first part references the previous step. So the previous step is called change type one. So, it says from change type one give me, and then it gives me a list. And this list is in curly brackets. So from that you can see we are in the table sort of functions. We are selecting columns, we’re selecting this table, and we’re saying we just want these columns.

Now, incidentally, if you don’t want any step to be code in what you are calling it, that’s fine, no problem. Just right and click on it, and go to rename. So, I’m going to call this Create Errors, cause that’s all that step did. And now you’ll see that this function formula is calling table dot select columns, Create Errors. This little symbol, which is called the hash or the pound sign is used to say what follows is an identifier. So something which is called Create Errors, give me this range of columns.

Now, just as there is something called table dot select columns, there is also something called table dot remove columns. So, if I go into remove columns, we’ve got no dialogue box here. I could either remove the column or columns that I’ve highlighted. So that’s removed the first column and you can see the formula table dot remove columns, and I want that particular column removed. It’s still a list even though it’s a list of one, so it’s still in curly brackets. Or you can say I want every single column to be removed apart from this particular one. So remove other columns. And so we get exactly what we had a few minutes ago, table dot selects columns, even though we’ve clicked on remove other columns. So, you can see in this interface there are many ways to do exactly the same thing.

Now, while we’re talking about columns we can also rearrange the columns. So, let’s say I wanted column four to become first. Well, all I do is highlight column four and just drag it all the way to the left. And you’ll notice that that becomes another step, and the formula’s called table dot reorder columns. So, this is quite useful because let’s say I wanted to reorder a lot of columns. Well I don’t have to go round dragging each all over the place, I can just change the formula. So, I want column thirteen to be after column four. No problem. I’ll just change the formula, it’s done. Equally, if I was now dragging column two at the very beginning, you’ll notice that it has not created an additional step. What’s it’s done is changed the formula for the previous steps. I don’t think, well I’m going to move one column to the left and then I’m going to use a different column to the middle and then another column to the right, that that’s going to create three steps. It’s not. The computer is intelligent enough to go ah, let’s add this to the previous step. So in this video we had a look at how to highlight columns, either by just clicking on them, or by go to the columns. Incidentally, you go to column is also available in the view section. If you want to use it there. We also removed any columns that we didn’t want, we could either choose the columns we wanted to keep, or we could highlight the columns and say I want to remove these columns, or remove other columns. And we also saw how to reorder the columns as well. And the vast majority of what we’ve done didn’t require one single typing of M-code. No formulas whatsoever. It created them. And again, how much you want to use them is down to you. I’ll keep mentioning them, but when we’re just using the interface you can completely forget about them if you so wish.

104. Home Menu – Reduce Rows and Use First Row as Headers

So, in this video we’ll have a look at how to actually correct the problem that we’ve got here. In that t\he wrong columns have been promoted to the headers. So, you notice that we have a promoted header all the way up here and it’s promoted the very top row. And quite often that makes sense and that’s what the computer did automatically. But in this particular case the computer got it wrong. Because we just had this funny sort of header at the beginning and then it was the table. So, what we’re going to do is go back a few steps. So, we’re going to delete this creating arrow step, that we put on, and this change type, and this promoted header. So, now we’re back to the original data.

Now, notice off in the Transform section we’ve got ‘use the first row as headers,’ and there’s also the vice versa the opposite ‘use headers as the first row,’ in case the computer makes a mistake. But how do we get row four to be the first row? Well, what we can do is delete the first three rows. And we do that by reducing rows.

Now, we have got keeping rows, and we got removing rows. So keeping rows is saying, I want to keep only the; so really it’s missing the only bit. I want to keep only the top six rows. So, you can see the formula there. Table dot First N, name of step, comma six. And if you’re wondering why it’s called CDs Sheet, well the navigation has got CDs and Sheet Data.

Now, if I want to adjust this and say, no I want eight. Then you can click on the Settings, and say, no, go over to that, I want eight rows. Or maybe you want to keep the bottom most rows. Very unlikely to be honest. Or you want to keep a range of rows, say from rows three to five. So, we’re getting closer, we want to keep from row four downwards, but that didn’t quite work. And in fact, when I said three to five, I meant row three, four, five rows. If you notice the formula table to range, it actually says comma two instead of comma three. That’s because M uses a zero counting system. So, if I was to count numbers and zero it would be zero, one, two, three, four. So the third row is zero, one, two. You can also keep duplicates, such as all of those rows which have duplicates. But duplicates in the highlighted columns.

So, for instance, if I wanted to have all of those which had duplicates in ten romantic works and nothing else. Well, let’s say I wanted to keep the first 20 rows, and that I just want all of those which have a duplication in this column. Then I can keep the duplicates. And you can see its kept the row at the top and its kept the 10 romantic works as well. But quite often you won’t be keeping rows, you’ll be removing rows. And to remove rows happens exactly the same way. We’ve also got an additional option which is remove blank rows, and again, this is based on the entirety of the row. If it’s completely blank it gets removed. So, what do we need in this particular option.

Well, we need to get rid of the first three rows. And we do that using Remove Rows, remove top three rows. But we can also remove the bottom rows, we can remove every alternate row or maybe something else. So, we can say, actually I want to remove; want you to go to the fourth row. I want you to remove two and I want you to retain three for instance. And you can see 18, 19, 20; remove two, 21, 22. So, you can see that how it’s worked. Very rare that you will be using those. Remove errors, you might be using more often. So, if I change this so that they’re all numbers, that creates a lot of errors because there’s a lot of text there which can’t be numbers. I can then remove all of the errors. So everything that contained an error in that particular column. So, in all of this, we’re using things like M formulas. Like, table.alternativerow, table.distinct, table.removerows with errors. So, let’s go back to our specific example. I want to remove the top three rows. So, I’m going to click on remove rows, remove top rows and remove top three rows. So that uses table.skip. Not table.revmove rows, as you might expect; but table.skip. So, we are skipping the top three rows.

Now, you’ll notice that in the formula there is a more advanced version, where you can skip rows that say remove rows where there is a certain condition. But that’s a bit outside this particular course. Have a read of that if you’re interested in the documentation. So now I have got the first three rows skipped. I am now going to promote this now top row to be my header. So use first row headers and now finally we have changed this so that the information makes since. The computer’s also done something else called change type, we’ll have a look at that in the transform section. But we now have the column headings we want. We no longer have this column 2, column 3, column 4. So, lets close and apply. We go to the fields and you can see the fields change from column 10, column 11 and column 12 and so forth. And what that change type has also done is set some of these date fields.

So, we’ve not got date hierarchies automatically added into all of this. So, when you get your data, the computer will quite probably have promoted headers. It will have put the top row of your data into headers, if you’re taking this from say an Excel table. It may have got it wrong. You may have to remove certain rows first, before you can then promote the headers.

So, this is a real reason why it’s useful to have this as a Macro, so that if you have new data coming in, in the same format, you can just replace the old data with the new data and it would run through this routine. It will take off the top three rows. It will promote it as headers. And you will have the latest data immediately at your fingertips. So, this is reducing rows and using the first row as headers.

• Category: Uncategorized