{"id":2268,"date":"2016-02-05T15:12:12","date_gmt":"2016-02-05T14:12:12","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2268"},"modified":"2024-02-06T16:08:52","modified_gmt":"2024-02-06T15:08:52","slug":"unpivot-data-using-excel-power-query","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/unpivot-data-using-excel-power-query\/","title":{"rendered":"Unpivot Data Using Excel Power Query"},"content":{"rendered":"<p><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> is a<strong> free add-in<\/strong> created by Microsoft for Excel 2010 (or later) and\u00a0you can download and install it for Excel 2010 and 2013:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/install-power-query-with-excel-2013\/\" target=\"_blank\" rel=\"noopener noreferrer\">Click to see tutorial on how to install Power Query in Excel 2013<\/a><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/installing-power-query-excel-2010\/\" target=\"_blank\" rel=\"noopener noreferrer\">Click to see tutorial on how to install Power Query in Excel 2010<\/a><\/p>\n<p>In Excel 2016 it comes built in the Ribbon menu under the <strong>Data<\/strong> tab and within the <strong>Get &amp; Transform<\/strong> group.<\/p>\n<p>&nbsp;<\/p>\n<p>Power Query allows you to extract data from any source, clean and transform the data and then load it to another sheet within Excel, Power Pivot or the Power BI Designer canvas.<\/p>\n<p>One of the best features is to\u00a0Unpivot Columns.<\/p>\n<p>What that does is transforms\u00a0columns with similar characteristics (e.g. Jan, Feb, March&#8230;) and puts them in a unique column or tabular format (e.g. Month), which then allows you to do further analysis using <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/101-excel-pivot-tables\/\" target=\"_blank\" rel=\"noopener\" title=\"Pivot Tables\" data-wpil-keyword-link=\"linked\">Pivot Tables<\/a> which was not possible before unpivoting.<\/p>\n\n<p>Here is how this is done:<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/JP3G8rko9M8?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><\/p>\n<p><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><span style=\"text-decoration: underline; color: #0000ff;\"><a class=\"IRPP_minimalist icon-download\" style=\"color: #0000ff; text-decoration: underline;\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/Unpivot.xlsx\" rel=\"\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Unpivot.xlsx<\/span><\/a><\/span><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> Highlight your data and go to <em><strong>Power Query &gt; From Table &gt; OK<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/from-table-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Unpivot Data Using Excel Power Query\"  class=\"alignnone wp-image-2273 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/from-table-1.png\" alt=\"Unpivot Data Using Excel Power Query\" width=\"735\" height=\"125\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/from-table-1.png 735w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/from-table-1-300x51.png 300w\" sizes=\"(max-width: 735px) 100vw, 735px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> This opens the Power Query editor and from here you need to <strong>select the columns that you want to unpivot<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/select-column-in-editor.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Unpivot Data Using Excel Power Query\"  class=\"alignnone wp-image-2274 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/select-column-in-editor.png\" alt=\"Unpivot Data Using Excel Power Query\" width=\"944\" height=\"255\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/select-column-in-editor.png 944w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/select-column-in-editor-300x81.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/select-column-in-editor-768x207.png 768w\" sizes=\"(max-width: 944px) 100vw, 944px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong><\/span> You then need to go to the <em><strong>Transform<\/strong> <\/em>tab and select<em> <strong>Unpivot Columns<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/unoivot-columns-power-query.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Unpivot Data Using Excel Power Query\"  class=\"alignnone wp-image-2275 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/unoivot-columns-power-query.png\" alt=\"Unpivot Data Using Excel Power Query\" width=\"588\" height=\"127\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/unoivot-columns-power-query.png 588w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/unoivot-columns-power-query-300x65.png 300w\" sizes=\"(max-width: 588px) 100vw, 588px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong><\/span> Go to the<em><strong> File<\/strong><\/em> tab and choose <em><strong>Close &amp; Load<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/clos-load-power-query.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Unpivot Data Using Excel Power Query\"  class=\"alignnone wp-image-2277 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/clos-load-power-query.png\" alt=\"Unpivot Data Using Excel Power Query\" width=\"241\" height=\"355\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/clos-load-power-query.png 241w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/clos-load-power-query-204x300.png 204w\" sizes=\"(max-width: 241px) 100vw, 241px\" \/><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5:<\/strong><\/span> This will load and <strong>open the unpivoted data into a new worksheet<\/strong> with your Excel workbook. \u00a0Now you can go crazy with your super analytical work, using Pivot Tables etc<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/unpivoted-data.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Unpivot Data Using Excel Power Query\"  class=\"alignnone wp-image-2278 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/unpivoted-data.png\" alt=\"Unpivot Data Using Excel Power Query\" width=\"318\" height=\"412\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/unpivoted-data.png 318w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/unpivoted-data-232x300.png 232w\" sizes=\"(max-width: 318px) 100vw, 318px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline; color: #0000ff;\">THIS POST WAS INSPIRED BY THE FOLLOWING PODCAST SHOW:<\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/podcast\/007-data-cleansing-analysis-with-oz-du-soleil-excel-podcast\/\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" title=\"Unpivot Data Using Excel Power Query\"  class=\"alignnone wp-image-2280 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/Oz_Podcast_Banner.jpg\" alt=\"Unpivot Data Using Excel Power Query\" width=\"728\" height=\"90\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/Oz_Podcast_Banner.jpg 728w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/Oz_Podcast_Banner-300x37.jpg 300w\" sizes=\"(max-width: 728px) 100vw, 728px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Power Query is a free add-in created by Microsoft for Excel 2010 (or later) and\u00a0you can download and install it for Excel 2010 and 2013: Click to see tutorial on how to install Power Query in Excel 2013 Click to see tutorial on how to install Power Query in Excel 2010 In Excel 2016 it [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17187,"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":[121,120],"tags":[88,117],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2268"}],"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\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=2268"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2268\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17187"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=2268"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=2268"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=2268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}