2.1 Introduce Get and Transform data - Video Tutorials & Practice Problems

<v Instructor>Before we get going,</v> let's quickly review the data life cycle then move into where getting transformed data helps both the data consumer and data producer. In Excel, we may have a business process such as sales commissions that we're trying to calculate. Before we can go and calculate sales commissions, we would likely first need to get some sales data. So we would, as a data consumer connect into a source that would provide us that sales data. Once that data is loaded into Excel, we may then act as a data producer to go ahead and calculate commissions based on that data. Once you've gone through the process of calculating commissions, we then may use the sales data and the commissions data downstream for reporting. So at that point, we may then act as a data consumer to consume both those sources and do some reporting. So how does all of this look inside of Excel? We can see on the diagram here that we're mapping out the data source to target locations inside of Excel. So starting on the far left hand side of the diagram, we can see that like any organization we have all kinds of different data sources and we may wish to bring any number of these data sources into our Excel worksheets. So up above we have the gray line that says Get and Transform Data. So inside that suite of functions is where we can then perform what is called ETL. ETL stands for Extract, Transform and Load. Extract means extract the data from a source system that you were interested in. Transform is where you take that data and cleanse it, add to it, remove rows, remove columns and get that data ready for whatever you're going to do with it. And then Load basically is where do you actually wanna load this data to? We're gonna do all that work with the Power Query Editor inside of Get and Transform at various parts of this lesson. Once data is loaded, we have a few places where we can actually choose to load it to. So we can choose to load data into Native Excel structures such as an Excel Table, a Pivot Table Report or a Pivot Chart or we have the option to only create the connection to our source and the transformations that we have within that connection. We also have the ability to load data into the Excel Data Model or what is also known as Power Pivot. So you may ask yourself, why would I make a decision to load to one versus the other? And if you wanna load data to the Excel Data Model, one of the primary reasons that we do that is the Excel data model can handle more than Native Excel can handle in terms of row capacity. Native Excel structures are limited to 1,048,576 rows. So just over a million rows worth of data. If you did need to load more data than that into Excel for your purposes, Native Excel structures wouldn't work for you and you may wanna take a look at using the Excel Data Model. We won't be covering the Excel Data Model in this particular fundamentals course but we wanted to ensure that we highlighted in this diagram for completeness purposes so that you get a sense for all the different places we could put things inside of Excel. To round out the diagram, we'll notice the gray line right above the Native Excel blue bubble. And these are all the Excel functions which you've likely used for some period of time if you've been playing around with Excel. And the gray line right above the Excel Data Model is the language called DAX, which stands for Data Analysis Expressions. It's the form of the language that we use for the Excel Data Model. And once again to recap, we're not going to actually do any coverage of the data model outside of this introduction in terms of showing where it fits into the landscape of Excel. So now that we've gone over this high-level overview of how data moves from sources into individual target locations within Excel, let's go ahead and do a demonstration of this. So what I've got here is a blank Excel workbook and we can see right now that we are on the Home tab. To get to the Get and Transform data section, we are going to go over to the Data tab so click on the Data tab here. And now we'll see on the left-hand side here a group of functions called Get and Transform Data. What you do here is go to the Get Data button right here, click on that little drop down box and we're gonna see there are a number of different sources that we can connect into. These are all grouped up by different source types so I can get data from file types. So from Excel workbooks, CSV files, XML documents, JSON file and you can read through the rest of the list. We can get data from databases such as SQL server Database and Access Database and the list goes on there. We can get data from Azure sources, from online services such as a SharePoint list or from other services which may include things like webpages or ODBC connections. So in this particular example what we're gonna do is we will go up to the file type here and the first data type that we wanna connect into is the Text/CSV file type. So we'll click on that. We will see the Import Data dialog box pop up. And what we wanna do here is we have a number of different data files which we can connect into. So I'm gonna click on this Sales Territory one right here which is a Excel CSV file. I'm going to then click this Import button right here. And we will get presented with a preview of some information around this file. So let's see, this is the name of the file, it's a CSV file. It gives us a preview of the data and the columns. We can also see the file origination. We can see that it picked it up as a Comma delimiter. And if we click it in that box, we can see a number of other different delimiter types. This one chose that correct one. And then we can see Data Type Detection. So what is happening here is the data types for each one of these five columns will be determined based on the first 200 rows in each one of those columns. Now, in my experience this works really well for doing data type detections. But if you really wanted to, you could click this drop own box here and ask it to base the data type detection on the entire data set. And just before warned if you have a large data set, this may take some time. Or we may say, Do Not Detect the Data Types. I will do that this later on as the developer within the Power Query Editor. for now, we'll just leave it on based as first 200 Rows. And now what we wanna do now that we're satisfied with the file that we're connected into and how everything was all picked up by default. We can go down to the Load button down below here and click this drop down box next to the word Load. And what this is going to allow us to do is ask us where do we want to load our data to? So I'm gonna click Load To and we're presented with the Import Data dialog box. We can see that by default, it is choosing to load the data into a Native Excel table. And down below here it's asking us, where do we wanna put it and is choosing to put it onto a new worksheet. Alternatively, we could put that data into an existing worksheet if we wanted to. But in this example here, we're not actually gonna physically load this data anywhere just yet. So let's go through the rest of the list. We could choose to load our data to a PivotTable Report. We could load it to a PivotChart. Or we have the ability to Only Create the Connection to the data itself. And this would include any transformations that we made to the data in the Power Query Editor. And at this point in time, we have not done any yet so we'll just choose Only Create Connection. And you're gonna notice that these boxes down here grid themselves out because we're not gonna put the data in there yet. I do wanna highlight this last check box here it says, Add this Data to the Data Model. So if you recall back to the data source to target locations diagram, we talked about earlier on in this particular sub lesson, we could choose to load this data to the Excel Data Model. If we want to do that, we would simply check that box and the data would then get put into the data model itself. We don't wanna do this so I'm gonna uncheck that box. And at this point in time, we're ready to click OK and what we're gonna see here is now over in the Query &amp; Connection pane here that we have a brand new query that has taken on the query name that was borrowed from the file name. And let's take a look at a few properties within this particular query. So if I click on this, we will see that we have got a preview. We can get a preview of the data with inside of Excel here. Oh, there we go. It says, here's the file name, here's the columns and here's the sample of the data. We can see some other metadata based on this that says there's five columns with these names. It lets me know the data was last refreshed at this particular time. The data has not been loaded anywhere and here's the actual data source. So one thing that I do want to do with this particular query is actually wanna give it a good name. I'm really big on naming conventions and making sure that we choose good query names and as we'll see later on good column names early on in the process so that we start promoting some maintainability of what we're building up. So I'm gonna click on these three dots right here and go down to Properties, click on that. So I'm gonna go ahead and give it a little bit nicer name and go ahead and click OK. And now we're gonna see the query over here has been renamed to Sales Territories. So I do wanna highlight that these are actually at this point in time called queries cause the query is just connecting into the source and performing the transformations on the data that you want to transform. But it is actually not loading in any physical structures at this point in time. So we can see here now that the proper name has been taken on and that we have the query set the way we want and that then brings us to the end of Sub Lesson Number One.