The power Excel workflow - Microsoft Excel Tutorial

- [Instructor] All right, let's kick things off by taking a look at the Power Excel Workflow. Now, when I talk about Power Excel, I'm referring to Excel's Business Intelligence tools. It's a whole suite of tools, all of which are available directly inside of Excel provided that you have a compatible version. So the great thing about those tools, and the great thing about this course, is that you don't need any additional software or tools to do some really powerful things. Now, one of the most complicated and confusing things for me when I started learning this stuff was just understanding A, what these tools actually are and what they do, and B, the role that they play as part of this broader workflow. So I'm hoping that this will help to clarify and simplify things for you a bit so you don't go through the struggle that I did. Now, it all starts with your raw data. And this data can come from virtually anywhere. You know, whether you've got flat files like CSVs or text files, tables directly in Excel, connections to database, folders containing multiple files, even things like streaming sources or connections to web data through APIs, these BI tools do an excellent job connecting to pretty much anything you can throw at them. So once you've got your raw data, the next step is to actually import it into Excel, and we use Power Query to do that. Now, I'm a huge Microsoft fan. I'm probably the biggest Excel fan you'll ever meet in your life. But one thing that Microsoft has a habit of doing is renaming things over and over. And these BI tools are no exception. So if you're using one of the newer versions of Excel like Office 365 or Excel 2016, you probably won't see the words Power Query anywhere. Instead, you'll see these tools under the Data tab under the Get and Transform section. So just keep that in mind. Those two things are basically one and the same. Now, the purpose of Power Query is to connect to those data sources, import it into Excel, and then apply all sorts of shaping and transformation tools. Now, this process is called ETL, or extract, transform, and load. Now, one thing that's really cool about Power Query is that it works kind of like VBA, like recording a macro, where it will save each step that you're taking and then allow you to replay or replicate those steps so that you can automate this flow of data from your raw sources. Now, once you've loaded up and shaped your raw data through Power Query, the next step is to work with your Data Model. And this is where you create your table relationships. You blend all those sources together. You can add calculated columns, define things like hierarchies and perspectives. This is really like the infrastructure engine behind the scenes, and it's also where your data gets stored and compressed. So we'll talk all about this, but Data Model is an incredibly powerful new tool that's going to really change the way you work with Excel. And then the last piece here is Power Pivot and DAX. This is where the analytics really comes into play. So Power Pivot basically works just like a traditional pivot table, except it sits on top of your Data Model. So you can explore and analyze all of the fields and all the tables in your Data Model and also create really powerful calculated fields called measures using a new formula language called Data Analysis Expressions, or DAX. So those are the kind of the four steps in the workflow in a nutshell. The course is going to follow a very similar flow. We're going to talk about Power Query first, then go into Data Modeling 101, and then finally Analyzer Data Model using Power Pivot and some really powerful DAX functions. So let's get goin'.