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 & BI Training
4.6 instructor rating • 14 courses • 346,882 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 - Excel Power Query, Power Pivot & DAX

Learn Excel business intelligence (Power Query, Power Pivot & DAX) with a best-selling Excel instructor (Excel 2013+)

07:01:46 of on-demand video • Updated June 2020

  • 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 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 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 he 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 daks so will no longer be using these redundant a one style grid formulas like you're used to. We're going to use daks 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 screen shots. This is a screenshot from the data model with a work but 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 cells 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 row limit are 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 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 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 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 excels 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 will 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 that managed data model button and the data tools here and here I can actually see the data that is just loaded. And when I scroll all the way down boom there you go over 10 million rows. 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 or 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 showman's 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 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 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 of these applied steps that have been taken. We actually started with a very simple one column table and then through the course of the steps we added fields 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 Qwerty qwerty editing window. Now last but not least key benefit number for creating powerful measures with daks. So you're looking at here. This is the measure creation window and we're creating a new field here using it time intelligence function that automatically calculates a 10 day rolling average. Now these types of functions are the reasons why daks 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 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 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 inquiry and power pivot and that's why they're the best thing to happen to excel in 20 years.