Grouping Queries

Quick reference

• 00:05 As you start building more and more queries with Power Query.
• 00:08 One of the things your going to find is that you're going to end up with a whole
• 00:12 pile of queries in one area.
• 00:13 And it becomes a little bit difficult to keep them organized.
• 00:16 And I'm going to show you right now if I go and show the query pane for
• 00:20 this work book, what we actually have is five different queries here.
• 00:25 The challenge is, is it it's really hard to just look at this and
• 00:27 know which ones are actually pulling from a raw data source.
• 00:30 Which ones are being used as intermediary stage inquiries and
• 00:34 which ones are actually being landed in Excel tables or into the data model.
• 00:40 So it'd be nice if we could organize these a little bit better.
• 00:44 Now, we do have the ability to do that, and it's actually not that hard.
• 00:48 We can come up to the transactions query,
• 00:50 which reads from the transactions table here.
• 00:53 And we can right click on it and say, move to group, and create a new group.
• 01:00 Now, I'm gonna create a new group here called Raw Data.
• 01:05 And this is the group that I generally use
• 01:08 to actually connect to my individual data sources.
• 01:11 So the main purpose here is to connect to the data source and
• 01:13 do some minor refinements, but that´s about it.
• 01:17 Naturally, the COA Query for Chart of Accounts also belongs in this folder.
• 01:23 And you would think that you could just left click and drag that query.
• 01:26 But unfortunately, it doesn't work that way from this particular interface.
• 01:29 So in order to move it, we right click and we say Move to Group and
• 01:35 we choose to move to the Raw Data group, and there we go.
• 01:39 We've now got two queries put in the raw data folder
• 01:42 which we can collapse if we wanna see less or open up if we wanna see more.
• 01:48 We still have a few more queries though but
• 01:50 I'd like to deal with these ones a little bit different.
• 01:52 Because this isn't the only interface where we can actually do grouping.
• 01:56 Let's go and grab one of the queries, doesn't matter which one.
• 01:58 I'm gonna right-click and say Edit.
• 02:00 The key piece is I want to get into the query editor.
• 02:03 And when I'm here,
• 02:05 what I'm gonna do is expand the queries pane on the right hand side.
• 02:09 And what you can see here is that I can actually left-click and drag and
• 02:14 move my queries around.
• 02:16 So I do have drag and drop in this particular area,
• 02:19 to move my queries around at different places.
• 02:21 So that's kinda nice.
• 02:22 I actually prefer doing all of my grouping and organizing in this query pane
• 02:27 rather than one in Excel because it's got some more functionality.
• 02:31 I'm now gonna make another group for the left ante and the right antequeries.
• 02:36 Neither of these actually load to the worksheet.
• 02:38 They're both just staging queries that allow me to do
• 02:41 different things with the data before performing the full ante merge later on.
• 02:47 I usually store queries at these names, and
• 02:50 I'm gonna grab both of them by holding down my shift key.
• 02:52 I would usually store staging queries in its own group which I'm gonna call
• 02:57 staging queries.
• 02:58 So create a new group, we'll call this one staging queries and we'll say okay.
• 03:05 And you'll notice tha we can move both of them into the same group at the same time.
• 03:10 Now at the end I've got this other one query left over full ante which is in my
• 03:15 other queries.
• 03:16 I'm going to right click and move him to a group as well.
• 03:19 This one I'm gonna actually call something like data model or
• 03:25 load queries, something like that.
• 03:27 But you know what?
• 03:29 I actually prefer data model.
• 03:30 That makes me feel like it's going to the right place so
• 03:33 every query that I see in data model is actually gonna load somewhere.
• 03:37 Either to power pivot is we get into that technology or
• 03:40 into the Excel worksheet grid.
• 03:44 It´s also worth pointing out that if you have multiple layers of these guys.
• 03:48 You could always move this guy to a group and say,
• 03:51 you know what I want the new group here for, let´s call this one transactional.
• 03:58 We'll just set this up temporarily but you can see that we can actually set
• 04:02 a transactional group here and it pops it out of raw data.
• 04:06 But if I wanna move this entire group to raw data, I can right click move the group
• 04:11 and move it into raw data so that it's actually a nested folder.
• 04:14 Now I'm gonna move this guy out.
• 04:16 I'm gonna say, you know what,
• 04:18 transactions I don't really actually want you there after all.
• 04:20 So I'm gonna move you back to the raw data folder.
• 04:24 And then I'm gonna grab this transactional folder here and
• 04:27 I'm gonna delete the group so I can clean this things up as well as I go along.
• 04:32 Does this affect the performance on the output of any queries?
• 04:35 No, not at all, I can still come in, I can see any when I want.
• 04:38 But it gives me the ability to actually start collapsing things so
• 04:41 I can focus on just the query I wanna see at any specific time.
• 04:45 When I go back to close and
• 04:46 load, you'll notice that there's no actually even loads take place.
• 04:49 Because we haven't changed anything in the queries themselves,
• 04:51 just where they're actually organized.
• 04:53 But now, every time I open up my queries pane,
• 04:56 I can actually see them nicely organized.
• 04:58 So if I'm looking specifically for a staging inquiry, and
• 05:00 let's say that I have a workbook that's got 40 queries in it.
• 05:03 I can now quickly collapse Raw Data, collapse Data Model.
• 05:07 And now I'm just looking at the individual queries that I actually want to see so
• 05:10 that I can find the ones that I actually want.

Lesson notes are only available for subscribers.