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

98. Welcome to Part 2: Get and transform Data

Hello, and welcome to part two of this course. And in this part, we’ll be looking at the get and transform section of Power BI, which uses the M language.

So, you can see that most of what we’ll be doing is in sections one to three. So, we’ll be getting data from different data sources. So, really from now, all we’ve done so far is used Excel, and we’ll be continuing to use a lot of Excel. Simply, because I know that’s what most of you have got on your machines.

We’ll be looking towards the end of this part about other types, like for instance, SQL Server and SSAS, but will be largely sticking to data sources that I know you’ve got, such as text documents, for instance. We’ll be looking at profiling the data. So, identifying data anomalies and so forth, but the majority of the time, I think, we’ll be looking at cleaning, transforming and loading the data, which is part three of the Power BI requirements. So, we’ll be identifying keys, we’ll be evaluating column data types.

We’ll be combining queries. We’ll be using the M Code in the advanced editor. We’ll be resolving inconsistencies. We’ll be doing lots of transformations to the table structures. And then we’ve also got one additional item, which is applying AI Insights. So, I hope that you’re going to have a very insightful time with all of this, so you can see what you can do before you get to the visualisation stage. So, we’ll be looking at a big menu at the top of the screen, and we’ll be dividing it into sections. So, we’re looking at the home tab, for instance, for quite a few videos, and then moving on to the other tabs.

I hope you enjoy it, and now let’s get started with part two.

99. Introduction – Let’s Get Some More Data

Hello, and welcome to part two of this course. In part one, we had a look through Visualise Data, fairly extensive look, so now you’ll be able to get some data. Just load it in, and visualise it in all sorts of different way, and you’re able to share it as well.

Now, we’ve only had a brief look at Get Data, but there is a huge amount of hidden depth. As you can see, we can be using something called M formulas, so the mashup language that allows us to transform the data, since sadly not all the data that you receive is in the perfect format.

Now, we will still be skipping this Add Modelling, we’ll have a look at that at part three of this course, but for now, we’ll be concentrating on the Get Data, and then the practise activities will be asking you to Get Data, and then visualise it. So, we’ll be building on what we’ve previously gone through with all the visualisation, but we’ll be concentrating more on the Get Data. But at the end of the practise activities, you will have an end result, which is a visualisation of the data we just got. So the Get Datas we’ve previously seen is here in the Home tab, and you can see various common file formats.

Now, if you’re using Excel, we’ve also got a Get Data in the Data section. Now, this has been changing over the last few years, it used to be around here, but now it’s moved all the way to here, and you can see fairly similar sorts of types that we’ve got. I think the more extensive in the Power BI, but still, the main types are still there. So, if your type isn’t here, then you can click on More, and you can see the extensive number of file types that we’ve got access to in all of these different categories, File, Database, Azure, Online, and so forth.

So, what we’re going to do is just load at the moment, some data about some CDs that I own. So, you can see here the data, and what book is downloadable is called the Power BI data, and you can see there are quite a number of tabs in this book, and there’ll probably be more by the time you’ll actually see this because I’ll probably be expanding this. And this is downloadable from the download sections, right near the beginning of this course. So, you can see we have a number, we’ve got a file name, got some hyperlinks, and we’ve got some dates.

Now, the important thing about this data is it’s got some different data types, so we have got integers, we have got text, we have got dates, and that’s really why we’re using it, just for this brief look at the Power BI Get Data. We will be loading lots of different types of data into Power BI. Again, this will be suited to what we are looking at, so for now, let’s just get the data. We’ll go Home, Get Data, go to Excel, find the data that we want, might’ve just added it to this folder, so Power BI data, click open, and you’ll see the computer goes, okay, I know there are lots of different types of tabs, what is it you actually want?

Well I want the CDs, and you can see a preview of that, so that’s good, so I will check it, I will take it, and I’ll click load. Note that there is another option called Transform Data. So, if I load, is it literally just getting the data? And as you can see it’s in a bit of a sorry state. If I want to edit this data, I can click on the dot-dot-dot (…) next to it, and go Edit Query. And that will get me into the Power Query editor, this is the Get Data editor. But what I’m going to do instead, is I’m going to delete this data, and I’m going to add it again, and instead of just saying load, I want instead to transform the data, and that gets me into the Power Query editor. The Power Query editor is where we’ll be doing most of our work in the second part of this course.

Now, there’s quite a lot for us to explore in this, and we’ll have a look at the key points of this editor in the next video.

100. Exploring the Power Query Editor Interface

So, let’s have a look around the Power Query Editor window. So on the left-hand side, we’ve got the queries. So these are all of the get data that you have just loaded. And it could also be a query can be made up of another query. So maybe you have altered a query and maybe you have combined two queries together to form a third query. So there are all of your queries that you’ve got loaded are over here. And you can select any one by clicking on a different query. On the top, we have got several menus. We have got the File menu, so this contains things like Close and Apply, which you can also see here in the Home menu. What this does is it takes you back to the Power BI desktop and you’ll notice in the Power BI desktop, it says there are pending queries, changes in your queries that you haven’t applied yet. Notice that we haven’t got any queries whatsoever started from a blank slate. So, if I was just to go close, whether I go in the File or in the Home menu, then it will close the Power Query Editor and take me back to the Power BI desktop. But I don’t have my new query. So, I’ve got to click on apply changes and when this happens, it then loads the information in. You can see the information not done terribly well, column 10, column 11. Don’t worry, we will be correcting that very shortly. So, let’s go back into my query. So in addition to close and apply, we’ve also got other things in the Home menu. These are common things that you can do. So New Query, so you can get more information, you can Refresh. So maybe your spreadsheet or maybe you’re getting something from the web, maybe that’s changed. You can minus columns, reduce the number of rows that you see, in other words, filter in some way. You can Sort, you can Transform. So maybe you have a column that you’re splitting into several. And you can Merge and Append queries. So combining multiple queries into one.

Now, we’ll be going through the vast majority of this in a little bit of detail, with an appropriate data source. So, this is just a quick overview of everything. So there may look a lot of stuff there and there is, there’s also a lot that’s actually hidden away as well. The Transform and Add Column menus do have a fair amount of similarity. For instance, you can see Statistics here, and Statistics here. What the difference is, is that Transform takes a column or columns, and changes it. Whereas Add Column may take an existing column, and adds a separate column. Alternatively, it may just add a new column. So, for instance, maybe I wanted everything to be numbered starting with the number one. And View, we have a few other things in here. And again, we’ll be going through the vast majority of this in this part

Now, on the right-hand side, we have got the query settings. So first of all, we’ve got the name, so if you didn’t like the name of the query, then this is where you can change it. But this is the most important point. This is called Applied Steps. What this is, is the computer’s instructions of what to do with your data. So, it got the data, it did something called navigation, it then promotes the headers. In other words, it took what was in row one, and promoted it up, and then it gave certain columns a different type, a field type. Whether it’s number, whether it’s text, for instance. So, if you’re used to visual basic applications, then this is a sort of macro. It says, do step one, step two, step three, step four. One of the good things about it is you can go back to an earlier step and see what’s happened. So, here is my existing source, or here are all of the spreadsheets, and then I went to one specific spreadsheet, and here you can see row one we’ve got all of these, well, they’re catalogue, for instance. It then got promoted into the headers. And the file type, the column type you can see it says abc123. We’ll get into what that means later. But then it changed it into A, B, C. So, the good thing about all of this is because it’s a series of steps that the computer is doing, you can change those steps. So, suppose, I wanted to change which data source I was pulling from, well, you can click on this little star next to the source. And you can say, “Well it wasn’t this spreadsheet I wanted, it was another one.”

However, there is danger. Let’s say we’ve got four steps, A, B, C, D. Let’s say we took out step C. Is there a guarantee that A, B, D, would work? Possibly not. So while you can delete steps, it is quite dangerous to do so, unless you know what you’re doing. If you’re deleting the last step, well, a computer can do A, B, C, no problem. It’s just when you’re missing a step.

Equally, if you’re changing what was in a previous step, then you face the navigation so which spreadsheet to go to, you might find that there are some unpredictable results coming up. So, if I change this, for instance, to a different one, Construction, click okay.

Eventually, we get an error. The computer doesn’t know exactly what’s happening. So, be careful when editing previous steps. But we’ll go into lots of detail as to why you should, when you should in this part. We’ll also be going into why it’s good that the computer is recording this as a series of steps. If you found that you have got a fairly complicated data source, and you were making all these changes to get it exactly how you want, and then the data changes, well, that’s no problem. You just refresh your data, and the computer will still go through all of the various data sources, all of the various steps that you’re doing. So the steps are independent from the actual data. You can change the data and still have all of these functions happen to your data.

Now, the last thing I want to talk about, or just to preview, is this bit, the formula bar. Now, you’re probably quite use to a formula bar in Excel, but you’ll see that these formulas are something that you have never used before. And some of them can look really scary. In the next video, what we’re going to do is have an introduction to all of these functions, find out when you do need to know about it, and find out when you don’t.

And you might be pleased to know that for the vast majority of beginning to intermediate tasks, you can completely ignore this formula bar. It’s when you’re getting into the advanced things that you should get to know this. So in the next video, we’ll have a brief introduction to these formulas used in the Power Query Editor. Just before we do, I just want to say, if, for some reason, you can’t see this query settings, maybe it’s no longer there, you close it on a previous occasion, then just to get it back, just go to View, and query settings. It’s very important, and I will have this on screen all the time, basically, when I’m using Power Query Editor. With these queries on the left-hand side, if you don’t want to see them, just click on this left arrow, this less than sign. And it just collapses. So, it doesn’t actually get removed, but just collapses. And if, for some reason, you can’t see the formula bar, I don’t know why you would ever want to get rid of it, but if you did, it’s also there in View as a checkbox.

So in the next video, we’ll have a look at these formulas.

• Category: Uncategorized