Introduction to Power Query - Microsoft Excel Tutorial

Hello everyone, and welcome to Intro to Power Query. Now, if you've never used Power Query before, this is a great course just for you. Power Query can be this big tool but it doesn't have to be. So in this course rather than go through every feature, especially the ones I'm not entirely sure if you're going to use, we're going to focus on the use cases where you would use Power Query and the steps you would go through to solve those problems. And once you kind of have an idea of what those steps are, you will so easily realize that you can just add and remove steps to adapt it to what you need to do. So I've really identified three use cases which I think really represent about 80% of how you can use Power Query. We won't do any in this course. That will be for another course. So this is really just to get your hands dirty and to get started and to actually maybe save some time over other courses. So here's the module overview. We're going to start with a small history of Power Query. Don't worry, this isn't going to be a huge history lesson, but your ability to actually grasp quickly what it is and how to use it actually does rest on your ability to understand where it sits within the Microsoft Analytics ecosystem. So this will be actually really interesting. And I think that once you grasp that, you'll see that the tool is really, really easy to use. Next, we're going to do a data wrangling example. So that's going to be we have some, as Oz would say, crap data, but we have some data that come to us from maybe a system from a person and we need to adjust it to make it look better. So you'll see how Power Query replaces things like lefts and mids and rights, things that we used to do with formulas. Then we're going to talk about a connecting data sets example. So here you're going to see how Power Query can replace things like VLOOKUP and other tools like VBA where we might have opened another Excel file and then used all these different mechanisms to combine them together. Finally, you're going to see a file from folder example, and what that's going to do is show you if you have let's say one file inside of a folder and that file is maybe the template, right? And the data is laid out in a specific way. That is always how it's going to be as you add more files. So it functions like a template, let's say, and let's say people add over time each month another file. So the layout is very similar across them. Similar enough that maybe you could just use one example file and then apply whatever steps you need to across the rest of them and then merge them into one table when complete. So those are the three major use cases, which I said represents about 80% of Power Query if you've not used it. So you know, the way I train is a little bit different. I've come to realize this over the years. If you want to make the most out of this, I would tell you you should take notes. I and to be fair, like I don't always take notes when I do online courses, but I've just found others when they take my classes, they like the notes that come up and I give them plenty of notes and things to remember. My next tip would be to focus on problem solving, which is to say I'm just going to ask you not to try to necessarily figure out my next steps. I think what sometimes happens is when I train some people have been taught to do certain things in Excel and they don't really, since they sort of focus on how they've been taught in the past to make a solution, they'll take extra steps that I haven't said to do and kind of get stuck and say well, where do I go next? Not realizing they did it. And maybe it's just because I think differently. I'm always thinking, well, where did I apply to take that step? But in any case, it really just kind of relax your mind and be open to seeing how what I'm doing solves a specific problem and that you can actually do the same things when you think that way. And then finally, you have to practice. I have some walkthroughs and guides as part of this class, but you know, I come up with a trivial use case because what I want you to do is sort of see how we can bring all these things together. But in real life, there's so many different ways they can come up. So really, I want you to think about going forward, what are the types of problems you could start solving with this and just, you know, when you're just sitting there, let's say you're in New York, you're on the train or you're driving, you know, take a moment and think, well, where can I use this? How could this save me time? Because Power Query can do a lot of that. I think with the name Power Query, it can be daunting but it's actually one of the simpler parts of Excel. So with that, let's move on to the next module.