Power Query + Power Pivot: "Best Thing to Happen to Excel in 20 Years"

Maven Analytics
A free video tutorial from Maven Analytics
Award-Winning Analytics & Business Intelligence Training
4.6 instructor rating • 22 courses • 501,497 students

Lecture description

In this lecture I'll explain why Power Query and Power Pivot are so awesome that they've been called the "best thing to happen to Excel in 20 years" from industry experts. We'll talk about the ability to load hundreds of millions of rows, build data models to blend data across sources, automate your data loading and ETL process, and create powerful calculated fields using data analysis expressions (DAX).

Learn more from the full course

Microsoft Excel: Business Intelligence w/ Power Query & DAX

Learn advanced Excel for data analysis & business intelligence (Power Query, Power Pivot & DAX language. Excel 2013+)

07:01:46 of on-demand video • Updated January 2021

  • Get up & running with Excel's game changing data modeling & business intelligence tools
  • Learn how to use Power Query, Power Pivot & DAX to absolutely revolutionize your workflow in Excel
  • Master unique tips, tools and case studies that you won't find in ANY other course, guaranteed
  • Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor
  • Get LIFETIME access to project files, quizzes and homework exercises, and 1-on-1 expert support
  • Build pro-quality business intelligence solutions to blend and analyze data from multiple sources
English [Auto] All right, so there's a quote by this guy named Bill Jelen, he's known as Mr. Excel. He's an Excel MVP and one of the leaders in the field. And what he said was that power Querrey and Power Pivot were the best thing to happen to excel in 20 years. And I couldn't agree more. So let's talk through why that's the case and review some of the key benefits to these business intelligence tools. So first and foremost, we can now import and analyze millions, tens of millions, even hundreds of millions of rows of data directly in Excel from virtually anywhere. So that alone is a pretty huge game changer. Second, we can quickly build models to blend and analyze data across sources. So no longer do we need to actually mash them together with cell formulas like we look up or index and match. We can easily create relationships to analyze holistic performance across an entire data model. Third, we can now create fully automated data shaping and loading procedures with no VBA and no macros so we can connect to databases, preserve those connections, and then watch the data flow through our entire model and our entire process with literally just the click of a button. And then finally, fourth, we can now define calculated measures using data analysis expressions. It's a formula language otherwise known as DACS, so it will no longer be using these redundant A1 style grid formulas like you're used to. We're going to use DACS expressions to interface with our data model. They're more flexible, they're more powerful and they're more portable when you're working with power pivot. So I want to jump through this a little bit more in depth one by one, and maybe show you some sneak peeks and some demos just to make this a bit more tangible for you. So no one important analyzed millions of rose, take a look at these screenshots. This is a screenshot from the data model with a workbook query window. And this is showing that we had just loaded just under twenty five million rows of data. So ask yourself, when was the last time you loaded twenty five million rows of data into Excel? And if you've never used these power query and power pivot tools, the answer is probably never. So that's the beauty of this. When you connect to data with power, query and load it to sales data model, the data actually gets compressed and stored in memory. You can still look at it, you can still access it, you can still transform and analyze it, but it doesn't get stored in traditional worksheets. What that means is that no more million limit, we're now completely open to large data sets. And just to prove this to you, let's go ahead and open up a new blank Excel workbook. And in this workbook, I go into my data tools, grab a new query from a text file, don't worry, I'm going to walk you through all of these steps throughout the course. Essentially, what I'm doing here is I'm grabbing a text file called supermarket purchases. And what I want to do is load it directly to accelerate data model. And as soon as I hit load, what you'll notice is that this new pain appears on the right side of the screen. It's called our workbook queries Pain. And as you can see, it's showing me in real time how much of that data from the supermarket purchases text file is loading. So this is real time. This is not sped up in any way. And as you can see, we're already over four million rows worth of data from this text file. And the reason why we're able to load up so much data here is that we're not trying to dump it into a traditional spreadsheet or workbook sheet like you're used to. You know, if we were, we would be limited by that million road capacity and we wouldn't be able to put the data there. Instead, this data is going to go straight into Excel data model. And the data model is a compression engine that will allow us to access that data, but just kind of behind the scenes. So as you can see here, it's wrapping up its process and then as soon as this loads up, it'll be instantly accessible to me through the data model. So there we go. Looks like it just wrapped up. And now, just as a little sneak peek, I'm going to click the manage data model button in the data tools here and here. It can actually see the data that I just loaded and when I scroll all the way down. Boom, there you go, over 10 million, rose. So pretty cool stuff. Now, again, the second key benefit bill, data models to blend sources, what we're looking at here is an example of the data model in what's called diagram, view and diagram. View allows you to see your table is kind of as individual objects and then draw or create relationships between them. So instead of manually stitching those tables together with cell formulas, we're going to work with relationships to blend that data based on common fields. So let's jump back to Excel and I'll show you what that looks like. So here in Excel, I'm going to manage my data model, which opens up the data model window. And I'm in data view by default, I can just click over to diagram view. And what this does is it shows me exactly what my tables look like, how they're connected and how they form this broader model. And we're actually going to be building this very model that you're looking at here throughout the course. And I know it looks a little bit complicated, a little bit foreign. But trust me, with a little practice, you'll get the hang of this stuff in no time. Third key benefit, automate your data processing. So this is a screenshot from the power query query editor. And with this editor, you can filter, shape and transform your data before it even loads into the data model. And what's awesome about this, it's just like a macro. It records each step that you take automatically and saves it with the query and with the workbook connection so that every time you need to load this data, just press refresh. It runs through all those applied steps and boom, you've automated your process. And I'll pop back into Excel and show you exactly what this looks like in Excel. So here in my data tab, I can click the show queries button, and this is where I see all the files that have been loaded, let's just right click and edit the calendar one. And in here, what's interesting is you can see all these applied steps that have been taken. We actually started with a very simple one column table. And then through the course of these steps, we added fields, we edited things, we modified data types until we got to the final version of the table that we wanted to load. So again, all of these steps can be edited, can be deleted as you see fit. So this is the power query, query, editing window. Now, last but not least, key benefit number for creating powerful measures with DACS. So what you're looking at here, this is the measure creation window and we're creating a new field here using a time intelligence function that automatically calculates a 10 day rolling average. Now, these types of functions are the reasons why DACs is so powerful. You could never do anything like this using the old school calculated fields with a regular pivot table. And if you could, it would be kind of hacked together and overly complicated. So this makes it very clear, very simple, and enables a lot of really cool, powerful analyses that we couldn't do previously. So let's jump back to one more time. And here we're looking at an actual power pivot, all of these calculated fields here are measures and you can do some really cool things like, you know, make them respond to threshold selections. You can filter and segment your data just like you could with a normal pivot table. You can use familiar conditional formatting tools like data bars and color scales. It's basically like that familiar pivot table, but with a ton of additional firepower behind the scenes. So there you go. Key benefits for why you should use power querrey and Power Pivot. And that's why they're the best thing to happen to excel in 20 years.