A short history of Power Query - Microsoft Excel Tutorial

Jordan Goldmeier here, and we are going to go into the history of these power tools. It will be very quick, I promise you. It will be very interesting. At least that's what I think, because I think it's really going to actually show you how important Power Query is and why we really like it. So even if you've never heard of this term before, there's a term that I'm sure you're going to be very familiar with here soon. So business intelligence, what is it? Okay. Well, I'm not going to necessarily answer that question because, you know, the same thing is happening right now to data science. What is analytics? You know, I don't know. I don't quite know the answer to that question. Right. But I will say that what all of these and if you work in the data space and I think what has become synonymous with business intelligence is actually this workflow that is really at the core of what so many analysts do. So the workflow goes like this, right? You get your raw data and you got to do some transformation here, I'm going to call that TR. And then, you know, it goes into some final product, right? Usually, usually but not always that's a data visualization. Okay. So that is the workflow that seems to be as old as time. And when you think about it, even if you're not like an Excel wizard or an Excel master, you were probably doing this in some way. People sent you files. Okay. So we're going to get Excels here. That's what we're going to say. You got a lot of files. Sometimes you had to do an advanced filter on it, right? So some type of advanced filter. And then after all those steps, you were done and that did some presentation at the end. And what's interesting about this workflow that you might have been doing, so that is kind of the most basic of it. And it doesn't matter if you were just filtering things out and you were typing them in, it doesn't matter if you were using no formulas. The fact is you probably went through this three step process. And what's interesting here is that in terms of the use of data and technology and this three step processes that we see kind of as a theory of it, that it's just manifested across so many things. So, you know, it used to be you might have gotten data. Now when I say used to be, let's say '80s and '90s, you got it from a data warehouse, right? You did some SQL on it and then you just did something right. You put that in a report or, you know, if you worked in the data warehousing, right? You might have a cube process that runs overnight and then it gets reported back through some sort of presentation like a dashboard. You know, there's so many ways to say this, but functionally it is a workflow that is so synonymous right now with business intelligence. Okay. So let's bring it all back. And this is where we kind of take it into a history lesson. But don't worry, I say history lesson, then I get scared because I'm like, Oh my God, I'm going to turn them off. So it's 2010. What a great year, right? 2010 Microsoft releases what they call the Power BI Suite. And you know, if I get this this history wrong, I'm sorry, but I'm pretty sure it goes like this, which is there are technologies. One is called Power Pivot, one is called Power Query and one is called Power View. And what I'm actually going to do is I'm sorry about that, but I'm going to make this one Power Query and this one called Power Pivot, because I want to make this in a specific order. So one, two, three. So if you just think about the process that I talked about just a second ago, this is where it's all going to click for you. Okay. Power Query, Power Pivot and Power View. So what do they do? Okay. Well, this one allowed you to get data really easily, and then it allows you to sort of transform it. And then that's what Power Query allows you to do. So it's often called get and transform or in Excel 2016, you'll see it's called get and transform. Power Pivot allows you to connect it in interesting ways and make it easily reportable. And then Power View is kind of like the original visualization layer of Power BI if you've never used. But it's also like Tableau, but it allows you to visualize this data based off of what you did earlier. So what's interesting here is that this tool feeds into this, right, process which feeds into this. So if we were really to go back to all of it, okay, if we're just going back to the entire process of data, right. And then you do something to it, so you transform it and then you present it, right? Okay. Power Query over here effectively allows you to get to a point at which you can sort of transform it. Even though Power Query does allow you to do some transformation, so I don't want to say it's totally in the data spot, but what it does is it helps you move to the next workflow. And then Power Pivot helps you get to a Power View workflow. Now you may be thinking, wow, that's complicated. But where does Power BI come into it? Okay. So that entire toolset in 2010 was built into Excel, and why not Excel? Right? Because at the time, Excel was the best place and it still is the best place, right? Excel is the best place for all of this. It's exactly, you know, it's the place where everyone hangs out. It's got all the data in it. So of course it makes sense that Excel is where it all starts. But, you know, as data got bigger, well, Excel became too much, I guess. Well, let me rephrase that. The problem set became way too much to do just in Excel. So that entire thing here, this entire thing. So all of this here, right, if we zoom out. So this entire process here, this became a tool called Power BI Desktop. However, all three of these tools still remain in Excel. Now, I'll probably get some email for this. The best one that still really uses Excel to its fullest is Power Query. You could do Power View and Power pivot in Excel. I don't recommend it because it's like going to be deprecated here soon. It's just not really supported the same way Power BI is. They've taken these technologies and enhanced them. Okay. So you may be thinking is Power Query in Excel also known as get and transform the same as Power Query in Power BI Desktop. Are they the same thing? And the answer is, I bet you know where I'm going with this. The answer is yes. It is the same tool. So once you become good at Power Query, you are one-third the way good at Power BI. Isn't that exciting? So by just taking this course, you're going to be one-third better at Power BI and you didn't even really have to think about it.