{"id":4803,"date":"2016-10-20T23:19:59","date_gmt":"2016-10-20T21:19:59","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=4803"},"modified":"2024-02-06T16:09:00","modified_gmt":"2024-02-06T15:09:00","slug":"create-pivot-columns-using-power-query","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/create-pivot-columns-using-power-query\/","title":{"rendered":"Create Pivot Columns Using 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> lets you perform a series of steps to transform your Excel data. One of the most common steps I do, is I want to <strong>simplify my data and aggregate them together<\/strong> into something like this:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Create Pivot Columns Using Power Query\"  class=\"alignnone size-full wp-image-4808\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-04.png\" alt=\"Create Pivot Columns Using Power Query\" width=\"388\" height=\"66\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-04.png 388w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-04-300x51.png 300w\" sizes=\"(max-width: 388px) 100vw, 388px\" \/><\/a><\/p>\n<p>Thankfully <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> has an option that allows us\u00a0to create <strong>Pivot Columns<\/strong>!<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/W1vutJBgrkw?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\/2016\/10\/Pivot-Columns-1.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-1.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\">Pivot-Columns-1.xlsx<\/span><\/a><\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p>Let\u2019s go through the steps in detail:<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1<\/strong>:<\/span>\u00a0Our sample data consists of Sales Quarter and multiple sales values for each quarter. We want to sum them all up per quarter. Let us load our data into Power Query.<\/p>\n<p>(Make sure that your data is firstly converted into an <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/how-to-insert-an-excel-table\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel Table<\/a> by pressing CTRL+T and OK).<\/p>\n<p>Go to <strong><em>Data &gt; Get &amp; Transform &gt; From Table (Excel 2016) <\/em><\/strong><em>or<\/em><strong><em> Power Query &gt; Excel Data &gt; From Table (Excel 2013 &amp; 2010)<\/em><\/strong><\/p>\n<p><span style=\"color: #0000ff;\"><strong>Excel 2016:<\/strong><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Create Pivot Columns Using Power Query\"  class=\"alignnone size-full wp-image-4805\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-01.png\" alt=\"Create Pivot Columns Using Power Query\" width=\"534\" height=\"713\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-01.png 534w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-01-225x300.png 225w\" sizes=\"(max-width: 534px) 100vw, 534px\" \/><\/a><\/p>\n<p><span style=\"color: #0000ff;\"><strong>Excel 2013 &amp; 2010:<\/strong><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Replace Values Using Power Query\"  class=\"alignnone size-full wp-image-3239\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table.png\" alt=\"Replace Values Using Power Query\" width=\"777\" height=\"129\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table.png 777w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table-300x50.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/from-table-768x128.png 768w\" sizes=\"(max-width: 777px) 100vw, 777px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2:<\/span> <\/strong>This will open up the Power Query Editor.<\/p>\n<p>Within here you need to select <strong><em>Transform &gt; Pivot Column<\/em><\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Create Pivot Columns Using Power Query\"  class=\"alignnone size-full wp-image-4806\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-02.png\" alt=\"Create Pivot Columns Using Power Query\" width=\"578\" height=\"616\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-02.png 578w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-02-281x300.png 281w\" sizes=\"(max-width: 578px) 100vw, 578px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3:<\/span>\u00a0<\/strong>This brings up the <strong>Pivot Column<\/strong> dialogue box.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Create Pivot Columns Using Power Query\"  class=\"alignnone size-full wp-image-4807\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-03.png\" alt=\"Create Pivot Columns Using Power Query\" width=\"699\" height=\"313\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-03.png 699w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-03-300x134.png 300w\" sizes=\"(max-width: 699px) 100vw, 699px\" \/><\/a><\/p>\n<p>For the <strong>Values Column drop down<\/strong>, select the column name\u00a0from our data\u00a0that has the values in it&#8230;in our example it will be <strong>SALES<\/strong>.<\/p>\n<p><strong>Click on advanced options<\/strong> and this will bring up the <strong>Aggregate Value Function<\/strong>. \u00a0Here we select how the new cells should be combined.<\/p>\n<p>As we want to show the <em>Sales\u00a0Totals<\/em> for each quarter, we need to select the <strong>Sum<\/strong> option from the drop down box.<\/p>\n<p>Click\u00a0<strong>OK.<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p><strong style=\"line-height: 1.5;\"><span style=\"color: #ff0000;\">STEP 4:<\/span> <\/strong><span style=\"line-height: 1.5;\">Now you will see your changes take place and the data has now been<strong>\u00a0grouped\u00a0and summed!<\/strong><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Create Pivot Columns Using Power Query\"  class=\"alignnone size-full wp-image-4808\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-04.png\" alt=\"Create Pivot Columns Using Power Query\" width=\"388\" height=\"66\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-04.png 388w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-04-300x51.png 300w\" sizes=\"(max-width: 388px) 100vw, 388px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5: <\/strong><\/span>Click <strong>Close &amp; Load<\/strong> from the <strong>Home<\/strong> tab and this will automatically open up a brand new worksheet in your Excel workbook with the new\u00a0data.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-05.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Create Pivot Columns Using Power Query\"  class=\"alignnone size-full wp-image-4809\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-05.png\" alt=\"Create Pivot Columns Using Power Query\" width=\"488\" height=\"225\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-05.png 488w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-05-300x138.png 300w\" sizes=\"(max-width: 488px) 100vw, 488px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>You now have your new table with the total of each sales quarter. \u00a0That&#8217;s why they call it POWER QUERY!!!<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-06.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Create Pivot Columns Using Power Query\"  class=\"alignnone size-full wp-image-4810\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-06.png\" alt=\"Create Pivot Columns Using Power Query\" width=\"903\" height=\"413\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-06.png 903w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-06-300x137.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/10\/Pivot-Columns-06-768x351.png 768w\" sizes=\"(max-width: 903px) 100vw, 903px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline; color: #0000ff;\"><strong>HELPFUL RESOURCE:<\/strong><\/span><\/p>\n<p><a href=\"http:\/\/myexcelonline.thinkific.com\/courses\/the-ultimate-excel-power-query-data-cleansing-online-course\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" title=\"Create Pivot Columns Using Power Query\"  class=\"alignnone wp-image-4477 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/380x230.jpg\" alt=\"Create Pivot Columns Using Power Query\" width=\"380\" height=\"230\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/380x230.jpg 380w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/09\/380x230-300x182.jpg 300w\" sizes=\"(max-width: 380px) 100vw, 380px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Power Query lets you perform a series of steps to transform your Excel data. One of the most common steps I do, is I want to simplify my data and aggregate them together into something like this: Thankfully Power Query has an option that allows us\u00a0to create Pivot Columns! \ufeff download excel workbook Pivot-Columns-1.xlsx &nbsp; [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17318,"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":[190,189,117],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4803"}],"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=4803"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4803\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17318"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=4803"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=4803"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=4803"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}