PowerPivotPro is Coming to Atlanta

March 20 - 22, 2018

Registration for 2018 Public Training is now open!


**Use the discount code “3ORMORE” when signing up 3 or more people.

MARCH 20 - 21

Foundations: Power Pivot & Power BI

Instructor: Austin Senseman

Super charge your analytics and reporting skills with Microsoft’s dynamic duo. Designed to handle huge volumes of data, these tools will transform the way you work! Two Days in our class and you are EMPOWERED!

  • Learn Microsoft’s secret weapon behind Power Pivot & Power BI: DAX
  • Taught by Austin Senseman – PowerPivotPro Partner
  • You don’t need to be an IT professional – most of our students come from an Excel background
Atlanta Public Training Classes
Atlanta Public Training Classes

MARCH 20 - 21

Level Up Series: Advanced DAX

Instructor: Ryan Sullivan

The Advanced DAX Course was such a hit in the first half of 2017 that we’ve expanded the course to 2 days!


  • This advanced DAX training class is taught completely in Power BI Desktop
  • Students are encouraged to take our Foundations course and have hands on experience with the DAX language
  • Taught by Ryan Sullivan – Principal Consultant
  • Class material drawn from usage of Advanced DAX applications while consulting with hundreds of international firms


Level Up Series: Power Query for Excel & Power BI

Instructor: Ryan Bergstrom

The second class in the series is our Level Up Series is Power Query for Excel & Power BI.

  • Students are encouraged to take our Foundations course and have hands on experience with Power Query in Excel or Power BI Desktop
  • Taught by Ryan Bergstrom – Principal Consultant and Trainer
  • We will cover common to unique business challenges made easy with Power Query’s data wrangling capabilities
  • Intermediate to Advanced Level Power Query best practices distilled into easy to understand patterns to apply to your most common business challenges
Atlanta Public Training Classes
PowerPivotPro Logo

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!

Interested in Learning How to Do this Kind of Thing?

power query

Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.

CLICK HERE to claim your spot.

  Subscribe to PowerPivotPro!


Reid Havens

Reid Havens is a Principal Consultant for PowerPivotPro. His passion is collaborating with individuals and organizations to help them 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 Business Intelligence, Reporting, & Design. 

This Post Has 7 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