Pinterest Pixel

Split a Query into Two Parts in Power Query

Bryan
Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.  You can do a lot of things and sometimes we end up with a fairly long query! One cool thing is you can split a query into two parts to make it more readable!
Split a Query into Two Parts in Power Query | MyExcelOnline Split a Query into Two Parts in Power Query | MyExcelOnline

We will use an existing spreadsheet wherein we used Power Query to extract the names from this tutorial.

We extracted the names only from this:

Process Flat Data Using Modulo in Power Query

Into this resulting table:

Process Flat Data Using Modulo in Power Query

We want to split this query into two parts! Let us see how we can do it below.

Split a Query into Two Parts in Power Query | MyExcelOnline

Download excel workbookSplit-a-Query-Into-Two-Parts.xlsx

STEP 1: Let us edit the existing Query in the spreadsheet. Go to Data > Get & Transform > Show Queries

Split a Query into Two Parts in Power Query

Double click on the query (Table1):

Split a Query into Two Parts in Power Query

 

STEP 2: This will open up the Power Query Editor.

Our query contains two main sections:

  • Setup and Adding an Index Column
  • Using the Modulo to Filter rows

We want to separate these two sections as shown below.

Split a Query into Two Parts in Power Query

We want to split it. Right click on Inserted Modulo and select Extract Previous

Split a Query into Two Parts in Power Query

 

STEP 3: This is where the magic happens!

The steps on top will be moved to a new query. Type in the name Setup and Index.

Split a Query into Two Parts in Power Query

Now it is split into two parts! See the first query Setup and Index.

Split a Query into Two Parts in Power Query

It is then followed by the second query that has the modulo and filtering steps.

If you check the source of Query #2, you can see that its source is Query #1 Setup and Index. You have successfully split your query into two parts!

Split a Query into Two Parts in Power Query

 

How to Split a Query into Two Parts in Power Query

 

Free Excel Macros & VBA Webinar Training

 

If you like this Excel tip, please share it
Split a Query into Two Parts in Power Query | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Duplicate Columns Using Power Query or Get & Transform

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...