The power Excel workflow

From the course: Excel Business Intelligence: Power Query

Start my 1-month free trial
  • Course details

    Microsoft Excel includes a powerful feature called Power Query—also known as Get & Transform or Get Data—which provides fast and powerful data gathering and cleansing capabilities. In this course—the first installment in the Excel Business Intelligence series—follow along with experienced Excel trainer Chris Dutton as he shows you the robust capabilities of Power Query. Chris kicks off the course by outlining the power Excel landscape and spelling out when to use business intelligence tools like Power Query, Power Pivot, and DAX. He then dives into Power Query, explaining how to leverage key query editing tools to connect and transform data. Learn about basic Power Query table transformations, text-specific query editing tools, how to merge and append queries, and more. Chris also shares best practices for working efficiently with Power Query.

    Instructor

    • Click here to view Chris Dutton’s instructor page

      Chris Dutton

      Founder & COO, Maven Analytics

      • Chris Dutton is a certified Microsoft Excel Expert and analytics consultant.Chris has nearly a decade of experience working with Fortune 500 companies across automotive, retail, insurance, and travel verticals. He has developed award-winning business intelligence and data visualization tools, which have been featured by Microsoft, the New York Times, and the Society of American Baseball Research (SABR). As founder of Excel Maven, Chris has helped thousands of students learn how to use Excel as a dynamic and powerful analytics tool, and has developed personalized training programs for individuals, private groups, and businesses across the country. He graduated summa cum laude and received the Charles Bluhdorn Prize in Economics at Tufts University.

    Skills covered in this course

  • Welcome

    - [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'.

  • Practice while you learn with exercise files

    Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.

    Download the exercise files for this course. Get started with a free trial today.

  • Download courses and learn on the go

    Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.

    Watch this course anytime, anywhere. Get started with a free trial today.

Contents