Locked lesson.
About this lesson
This lesson shows how to create query groups so that you can keep your queries organized. Super useful for creating a self-documenting query chain.
Exercise files
Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.
Grouping queries.xlsx30.6 KB Grouping queries - Completed.xlsx
31.1 KB
Quick reference
Grouping Queries
An overview of creating groups of queries.
When to use
When you need to organize a list of many queries by grouping certain ones together.
Instructions
Getting started
- You can group queries in the queries pane inside the Power Query editor, or via Excel
- To show Excel’s Query Pane:
- Excel 2016: Go to the Data tab and select “Show Queries” or “Queries & Connections”
- Excel 2010/2013: Go to the Power Query tab and select “Show Pane”
Creating a new group
- Right click on a query --> Move To Group --> New Group…
- When the New Group dialog opens, name the group
Adding to an existing group
- Right click on a query --> Move To Group --> choose the existing group
- Drag and drop the query into an existing group (inside the Power Query editor only)
Creating sub-groups
- Method 1:
- Create a new group by right clicking on a query --> Move To Group --> New Group…
- Right click the group --> Move To Group --> choose the existing group
- Method 2:
- Right click the desired parent group --> New Group...
- Move the desired queries into the new sub-group
Hints & tips
- Groups can be used to keep queries organized
- Creating a “Raw Data”, “Staging Queries” and “Load” group can allow you to quickly scan the queries pane and make sure queries are loading to the correct destinations
- 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.