Welcome back P3 Nation for Part 2 of our Power Query (M)agic series! Today I’ll be addressing an issue many of us have experienced, what to do with the LAUNDRY LIST of PQ queries you have. Since it feels really awkward to say Power Query Queries…for the rest of this post they will be simply called queries, keeping it simple folks! Any respectable data model often ends up with a plethora of queries. The more queries you have, the harder it is to navigate or find the one you want. This is where today’s solution comes in! Smile

Very-Basic-Opened-Folder-iconTechnique #2 – Queries Are Like Documents In A File Cabinet

How do you organize documents? You put them in FOLDERS! If you’ve ever used Power Query to pull data into a data model in Excel or Power BI, then you know how many different queries it can have. The most I’ve had in a model I’ve designed is 78, crazy right… So it’s easy to imagine how a list of queries that long can get really complex, especially when you’re trying to find one to edit.

In PQ, Organizing Your Queries Is All About Making GROUPS!

A great (and easy) way that Power Query solves this issue is a feature that lets you add folders and even sub-folders. It’s a great way to organize and group your queries anyway you’d like. I’ll show you how to create these groups first, then I’ll explain the groups I typically like to make.

Giant hand man doesn’t like this uncategorized mess:

Power Queries PQ without Groups

STEP 1 – Right click anywhere in the Queries section and select New Group:

Power BI Power Query Add Folder

STEP 2 – Right click on any query and select Move to Group:

Power BI PQ Move to Existing Folder

End Result Folder Organization…giant hand man approves this technique!

Power BI PQ with Folders

Some of you may have noticed the groupings I created. Arguably there’s no “best” technique when it comes to organizing your queries, but I find this one very straight forward. I like to group my queries into three main categories.

  1. E – Data Sources
  2. T – Staging Tables
  3. L – Load to Data Model

Think of these as checkpoints along a journey, starting from where the data is coming from…and ending in the data model.

Umm, ETL? What is that? ELI5 please!

Some of you know what those fancy “E.T.L.” letters mean. Cheers to you guys – you’ve scored in the top quartile of nerd! If you don’t know what those letters mean, then here’s a quick overview. If you find your self writing TLDR often then here’s the short version. Your data lives somewhere, you have to go get it, clean it up a bit, and put it in your data model. That’s ETL! That’s exactly what Power Query is designed to do! Here’s the longer version …

Extract Your Data

Our journey starts with gathering up our data. The data source is the place where I store ALL connections to the data sources, with each getting their own unique query. Remember that beautiful feature I showcased in Power Query (M)agic Part 1: Always Have Good References? Well those reference queries go here, enough said. Smile

Transform Your Data

In the middle of our journey area the Staging Tables. They can also be called Transformation Tables or Mapping Tables, pick the terminology (dealers choice). These are where I put any tables that are used for transformations, joins, filters, etc… but aren’t actually LOADED into the Data Model. So to reiterate…transformations, custom mappings, and other miscellaneous COOL STUFF goes here. Some models might have lots of these, many models don’t have have any. Got it? Good, moving on.

Load Your Data

The destination of our wonderful journey! All roads lead to the data model. Simply put, if the query is being loaded as a table into the data model, it goes into the Load to Data Model group. You’ll notice that I have two sub-folders within this group as well, Lookup Tables and Fact Tables. Lookup Tables (Dimension Tables for the SQL-minded) are tables that are typically semi-static or infrequently updated (E.g. Customers, Employees, Calendars, etc…). Fact Tables (we also call them Data Tables!) are where the bulk of the data is kept and they usually grow or get updated frequently (E.g. orders, data recordings, time tracking, etc…).

More information about data modeling concepts can be found in another great post written by Rob called Data Modeling in Power Pivot and PBI. Alright that’s it for today’s post folks, hopefully this added a tool to your belt. So until next time folks!

  Subscribe to PowerPivotPro!
X

Subscribe

Reid Havens

Reid Havens is a Principal Consultant for PowerPivotPro and the Owner of Havens Consulting Inc. His main goal is to collaborate with individuals and organizations by helping them analyze data to understand their business. Using his formal backgrounds in technology and organizational management Reid has worked with various local and multi-national companies. He is also an adjunct professor at Bellevue College, guest lectures at the University of Washington, and provides corporate trainings centered around teaching Business Intelligence, Reporting, & Design. 

This Post Has 6 Comments

  1. Thanks we are already doing some version of this here at E-magination Info Solutions,South Africa. glad to know there we are speaking the same Query language ; )

  2. Nice one !
    Such a pain that we cannot remove this “other queries” folder.
    Also, it would be convenient to be able to “turn on/off load” & “include in the update” features at folder level !

  3. Hi,

    I am already using the queries organising method you mentioned above in the article.
    I use Excel 2016’s Get & Transform Power Queries Window.

    However, i notice one big issue with the query pane. (i not sure if the Power BI Desktop’s user experience the same issue):

    Under this so-called the “folder” grouping method, the grouped folders will always “expanded” instead of “collapsed” each time you re-open the excel file. It is really frastruating that the excel’s power query window doesn’t have a “collapse all” button for me to collapse all the folders or remember the last “collapsed form” when i save the workbook. I have to manually “collapsing” the folder one-by-one, especially if have have more than 50 root folders and sub folders…

    Any idea to help me frastruation?

    1. Hi Tommy,

      That’s a great observation. Unfortunately for now it does default to the expand all option, rather than allowing you to “save” them collapsed. Hopefully it’s an update soon!

Leave a Comment or Question