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

A free video tutorial from Maven Analytics
Empowering everyday people with life-changing data skills
Rating: 4.6 out of 5Instructor rating
40 courses
1,277,944 students
Power Query + Power Pivot: "Best Thing to Happen to Excel in 20 Years"

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 Excel Power Query & DAX for data analysis, prep, modeling & business intelligence, with a top MS Excel instructor!

07:05:47 of on-demand video • Updated May 2024

Get up and running with MS Excel's powerful data modeling & business intelligence tools
Learn how to use Power Query, Power Pivot & DAX to revolutionize your data analysis workflow in Excel
Master unique tips, tools and case studies that you won't find in ANY other course
Explore fun, interactive, and highly effective lessons from a best-selling Microsoft 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 query 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 VLOOKUP 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, calculate and measures using data analysis expressions. It's a formula language otherwise known as Dak's. So we'll no longer be using these redundant a one style grid formulas like you're used to. We're going to use Dak's 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 these 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 number one, important, analyze millions of rows. 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 25 million rows of data. So ask yourself, when was the last time you loaded 25 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 excel at 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, we 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 Excel's data model. And as soon as I hit load, what you'll notice is that this new pane appears on the right side of the screen. It's called our workbook queries pane. 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 4 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 capacity and we wouldn't be able to put the data there. Instead, this data is going to go straight into Excel's 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. It looks like it just wrapped up. And now just as a little sneak peek, I'm going to click the managed data model button in the data tools here and here. I can actually see the data that I just loaded and when I scroll all the way down. Boom. There you go. Over 10 million rows. So pretty cool stuff. Now again, the second key benefit build 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 tables 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, but 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 raw 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 Dak's. 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 ten day rolling average. Now, these types of functions are the reasons why Dak's 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 analyzes that we couldn't do previously. So let's jump back to Excel 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 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 query and power pivot. And that's why they're the best thing to happen to excel in 20 years.