What is Power Query and What Does it Do?

Quick reference

• 00:05 One of the things we know about building modern business intelligence or
• 00:09 BI solutions is that every single one of them starts from raw data.
• 00:14 But the challenge is that data could come from a lot of different places.
• 00:18 It could come from databases.
• 00:19 Maybe it comes from the Internet, or it could come from a text file,
• 00:24 or a CSV file, or even an entire folder full of files.
• 00:28 It can even come from email.
• 00:30 There is lots of different sources for data but the real truth is that
• 00:35 every single one of them requires some kind of cleansing and transformation into
• 00:39 a tabular format before we can actually expose Excel's most powerful tools.
• 00:45 And when we're talking about Excel's most powerful tools,
• 00:47 we're thinking about thinks like pivot tables and charts,
• 00:51 maybe even VLOOKUP statements and different stuff like that.
• 00:54 The challenge that we have is that, when you're interviewed for
• 00:58 your job to work with Excel, people will often ask you about your reporting skills.
• 01:02 They'll ask you do you know how to do this, do you know how to do that?
• 01:05 But they don't really focus and spend a lot of time
• 01:08 on the part that actually really, really impacts to majority of your solutions,
• 01:13 and that's this area right here.
• 01:15 And the reality is, we actually spend about
• 01:18 80% of our time cleaning up data to get it ready to use for analysis and
• 01:23 yet your interview is all about how you interpret data.
• 01:27 It's kind of an interesting little, kinda of flip top way of looking at things,
• 01:31 it's a little bit problematic.
• 01:33 What this course is about is trying to actually deal with that red box,
• 01:37 to give you some strategies to actually make that a little bit quicker.
• 01:41 The challenge we have with data actually comes in in several different places.
• 01:45 The first one is all about access.
• 01:48 Can you even get to your data at all?
• 01:51 And the reality is that sometimes database access is not even allowed inside
• 01:55 the company, and there could be a couple of different reasons for that.
• 01:58 Some of it could be around different security policies.
• 02:02 Some of it could be around confidentiality,
• 02:05 some of it could even be around classified information that you don't have proper
• 02:10 clearance to get to, right?
• 02:11 So this is some of the stuff we just can't get there.
• 02:14 But there is also other times when it is impossible to get to the data because
• 02:18 cuz technically, it can't be done.
• 02:20 Maybe there's no driver to get into the database system.
• 02:23 Sometimes, it's even worse than that and
• 02:25 the data we need doesn't even live in our systems at all.
• 02:29 I used to actually work as a controller for a golf course and
• 02:31 the number one driver for our business was weather.
• 02:34 And yet none of our point of sales systems captured weather.
• 02:37 We were all about capturing how many beers were sold, how many golf rounds were sold,
• 02:41 and yet we needed this information, which didn't live in our systems at all.
• 02:45 Other problems, your data could be dirty, it could be poorly structured, it could be
• 02:50 really badly shaped with lots of other garbage that's actually mixed into it.
• 02:55 It could be complicated, maybe it's actually nested into pivoted data,
• 02:59 where you want it unpivoted, maybe it's structured and sets.
• 03:03 And the other side is,
• 03:04 and this has becoming a bigger problem all the time, it could be high volume.
• 03:08 There's three Vs in big data, volume, variety, and velocity, and
• 03:12 all of these things, where data is coming at us super, super quick in huge volumes.
• 03:17 We need tools to actually make sense of the data and
• 03:20 actually be able to work with it in good old classic Excel.
• 03:23 It's just not up to that task anymore, and that's why we now have Power Query.
• 03:29 Power Query adds ETL to Excel, and
• 03:31 if you're not familiar with this three letter acronym, it stands for extract,
• 03:35 transform, and load.
• 03:40 So this is actually the process that you go through when you're building a business
• 03:43 intelligent solution.
• 03:43 You grab data from somewhere, you clean it up, transform it a little bit, and
• 03:47 then you load it into a worksheet so that you can actually work with it.
• 03:50 And Power Query actually allows us to make this job a little bit easier.
• 03:53 Where can we get our data from?
• 03:55 Well we can get it from places like files, we can get it from databases,
• 03:58 we can pull data from the web, from a Microsoft Exchange email server,
• 04:03 we can even pull data from other queries and this is far from an exhaustive list.
• 04:08 What is transforming to us?
• 04:10 It means the things that you're use to doing with your data.
• 04:13 It's cleaning it.
• 04:14 It's reshaping it.
• 04:15 It's removing a relevant data.
• 04:17 It's merging things horizontally like the VLOOKUP.
• 04:20 It's appending things like copy and pasting and
• 04:22 stacking tables in the big long tables.
• 04:24 And it's even generating new data that we actually need.
• 04:27 These are all things that Power Query can help us do and automate the task.
• 04:32 Where can we load it to?
• 04:33 We can load it to Excel tables, that's the standard default.
• 04:36 If you're working with things like Power Pivot you can load directly in
• 04:39 the Power Pivot state of model.
• 04:41 And you can also create your query so that it's preloaded and
• 04:44 ready to be called when needed landing in what we call a connection.
• 04:48 The reality of this is that this gives us some new tools to work with data.
• 04:51 We know that we've got these lumps of data on the left, and we know we need the table
• 04:55 on the right and in the past this was always a challenge in Excel.
• 04:59 We now have a rich array of information tools in one place to be able to go and
• 05:04 transform our data into the format that we need, but we've got one more thing too.
• 05:08 We now have one-click refresh, and this is
• 05:12 huge cuz we can actually invest our time in building a scenario that we need.
• 05:16 And then, when we're done, we click one button to refresh it next month,
• 05:20 so it just happens all over again for us, saving us a ton of time.
• 05:23 And, my friends, that is why Power Query is amazing and
• 05:27 definitely leads to happy analysts at the end of the day.

Lesson notes are only available for subscribers.