{"id":12411,"date":"2019-06-05T12:17:17","date_gmt":"2019-06-05T10:17:17","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=12411"},"modified":"2024-02-06T16:09:29","modified_gmt":"2024-02-06T15:09:29","slug":"split-a-query-into-two-parts-in-power-query","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/split-a-query-into-two-parts-in-power-query\/","title":{"rendered":"Split a Query into Two Parts in Power Query"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/installing-power-query-excel-2010\/\" target=\"_blank\" rel=\"noopener noreferrer\">Power Query<\/a> or Get &amp; Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.\u00a0 You can do a lot of things and sometimes we end up with a fairly long query! One cool thing is you can <strong>split a query into two parts<\/strong> to make it more readable<b>!<\/b><\/p>\n<p>We will use an existing spreadsheet wherein we used <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/process-flat-data-using-modulo-in-power-query\/\" target=\"_blank\">Power Query to extract the names from this tutorial<\/a>.<\/p>\n<p>We extracted the names only from this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Process Flat Data Using Modulo in Power Query\"  class=\"aligncenter size-full wp-image-11110\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/02\/Process-Flat-Data-using-Modulo-01.png\" alt=\"Process Flat Data Using Modulo in Power Query\" width=\"241\" height=\"503\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/02\/Process-Flat-Data-using-Modulo-01.png 241w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/02\/Process-Flat-Data-using-Modulo-01-144x300.png 144w\" sizes=\"(max-width: 241px) 100vw, 241px\" \/><\/p>\n<p>Into this resulting table:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Process Flat Data Using Modulo in Power Query\"  class=\"aligncenter size-full wp-image-11117\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/02\/Process-Flat-Data-using-Modulo-08.png\" alt=\"Process Flat Data Using Modulo in Power Query\" width=\"366\" height=\"155\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/02\/Process-Flat-Data-using-Modulo-08.png 366w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/02\/Process-Flat-Data-using-Modulo-08-300x127.png 300w\" sizes=\"(max-width: 366px) 100vw, 366px\" \/><\/p>\n<p>We want to split this query into two parts! Let us see how we can do it below.<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/bd1xyUK4s2o?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Split-a-Query-Into-Two-Parts.xlsx<\/span><\/a><\/p>\n<p class=\"line-two\">\n<\/div>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1<\/strong>:<\/span> Let us edit the existing Query in the <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-excel-templates-and-spreadsheets\/\" target=\"_blank\" rel=\"noopener\" title=\"spreadsheet\" data-wpil-keyword-link=\"linked\">spreadsheet<\/a>. Go to <strong><em>Data &gt; Get &amp; Transform &gt; Show Queries<\/em><\/strong><\/p>\n<p><strong><em> <img loading=\"lazy\" decoding=\"async\" title=\"Split a Query into Two Parts in Power Query\"  class=\"aligncenter size-full wp-image-12413\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-01.png\" alt=\"Split a Query into Two Parts in Power Query\" width=\"800\" height=\"240\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-01.png 800w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-01-300x90.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-01-768x230.png 768w\" sizes=\"(max-width: 800px) 100vw, 800px\" \/><\/em><\/strong><\/p>\n<p><em><strong>Double click<\/strong><\/em> on the query (Table1):<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Split a Query into Two Parts in Power Query\"  class=\"aligncenter size-full wp-image-12414\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-02.png\" alt=\"Split a Query into Two Parts in Power Query\" width=\"537\" height=\"243\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-02.png 537w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-02-300x136.png 300w\" sizes=\"(max-width: 537px) 100vw, 537px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2: <\/strong><\/span>This will open up the <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/50-things-you-can-do-with-excel-power-query\/\" target=\"_blank\" rel=\"noopener\" title=\"Power Query\" data-wpil-keyword-link=\"linked\">Power Query<\/a> Editor.<\/p>\n<p>Our query contains two main sections:<\/p>\n<ul>\n<li>Setup and Adding an Index Column<\/li>\n<li>Using the Modulo to Filter rows<\/li>\n<\/ul>\n<p>We want to separate these two sections as shown below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Split a Query into Two Parts in Power Query\"  class=\"aligncenter size-full wp-image-12415\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-03.png\" alt=\"Split a Query into Two Parts in Power Query\" width=\"1920\" height=\"455\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-03.png 1920w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-03-300x71.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-03-1024x243.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-03-768x182.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-03-1536x364.png 1536w\" sizes=\"(max-width: 1920px) 100vw, 1920px\" \/><\/p>\n<p>We want to split it. Right click on\u00a0<strong>Inserted Modulo\u00a0<\/strong>and select\u00a0<em><strong>Extract Previous<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Split a Query into Two Parts in Power Query\"  class=\"aligncenter size-full wp-image-12416\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-04.png\" alt=\"Split a Query into Two Parts in Power Query\" width=\"570\" height=\"541\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-04.png 570w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-04-300x285.png 300w\" sizes=\"(max-width: 570px) 100vw, 570px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3: <\/strong><\/span>This is where the magic happens!<\/p>\n<p>The steps on top will be moved to a new query. Type in the name\u00a0<strong>Setup and Index<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Split a Query into Two Parts in Power Query\"  class=\"aligncenter size-full wp-image-12417\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-05.png\" alt=\"Split a Query into Two Parts in Power Query\" width=\"616\" height=\"326\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-05.png 616w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-05-300x159.png 300w\" sizes=\"(max-width: 616px) 100vw, 616px\" \/><\/p>\n<p>Now it is split into two parts! See the first query\u00a0<strong>Setup and Index<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Split a Query into Two Parts in Power Query\"  class=\"aligncenter size-full wp-image-12418\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-06.png\" alt=\"Split a Query into Two Parts in Power Query\" width=\"1920\" height=\"813\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-06.png 1920w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-06-300x127.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-06-1024x434.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-06-768x325.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-06-1536x650.png 1536w\" sizes=\"(max-width: 1920px) 100vw, 1920px\" \/><\/p>\n<p>It is then followed by the second query that has the modulo and filtering steps.<\/p>\n<p>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!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Split a Query into Two Parts in Power Query\"  class=\"aligncenter size-full wp-image-12419\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-07.png\" alt=\"Split a Query into Two Parts in Power Query\" width=\"1920\" height=\"599\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-07.png 1920w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-07-300x94.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-07-1024x319.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-07-768x240.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/05\/Split-a-Query-Into-Two-Parts-07-1536x479.png 1536w\" sizes=\"(max-width: 1920px) 100vw, 1920px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #0000ff;\"><strong>How to Split a Query into Two Parts in Power Query<\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/100-39.html\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2017\/07\/530x300.jpg\" alt=\"Free Excel Macros &amp; VBA Webinar Training\" width=\"530\" height=\"300\" title=\"\"><\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Power Query or Get &amp; Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.\u00a0 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! [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17773,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[120,121],"tags":[117,273],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/12411"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=12411"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/12411\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17773"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=12411"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=12411"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=12411"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}