{"id":3278,"date":"2016-06-14T13:57:39","date_gmt":"2016-06-14T11:57:39","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=3278"},"modified":"2024-02-06T16:17:45","modified_gmt":"2024-02-06T15:17:45","slug":"transpose-using-power-query-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/transpose-using-power-query-excel\/","title":{"rendered":"Transpose Using Power Query"},"content":{"rendered":"<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/installing-power-query-excel-2010\/\" target=\"_blank\">Power Query<\/a> lets you perform a series of steps to transform your Excel data. \u00a0One of the steps it allows you to take is to\u00a0<strong>transpose data very easily<\/strong>.<\/p>\n<p><strong>Transposing\u00a0<\/strong>a data table is basically\u00a0<strong>rotating your data from rows to columns<\/strong>,\u00a0<strong>or from columns to rows<\/strong>. To further explain this concept, screenshots are provided below.<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/N4YMQunNLdc?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><a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Transpose.xlsx<\/span><\/a><\/p>\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> Select\u00a0your data and turn it into an Excel Table by pressing the <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/333-excel-shortcuts-for-windows-and-mac\/\" target=\"_blank\" rel=\"noopener\" title=\"shortcut\" data-wpil-keyword-link=\"linked\">shortcut<\/a> <strong>Ctrl + T <\/strong>or by going to<strong> Insert &gt; Table<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Transpose Using Power Query\"  class=\"alignnone size-full wp-image-3283\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-01.png\" alt=\"Transpose Using Power Query\" width=\"630\" height=\"386\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-01.png 630w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-01-300x184.png 300w\" sizes=\"(max-width: 630px) 100vw, 630px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2<\/strong>:<\/span> 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><strong>Excel 2016:<\/strong><\/p>\n<p><strong><em>\u00a0<a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Transpose Using Power Query\"  class=\"alignnone size-full wp-image-3284\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-02.png\" alt=\"Transpose Using Power Query\" width=\"618\" height=\"132\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-02.png 618w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-02-300x64.png 300w\" sizes=\"(max-width: 618px) 100vw, 618px\" \/><\/a><\/em><\/strong><\/p>\n<p><strong>Excel 2013 &amp; 2010:<\/strong><\/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 3:<\/span> <\/strong>This will open up the Power Query Editor.<\/p>\n<p>We want to rotate this data from\u00a0<strong>rows to columns.<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Transpose Using Power Query\"  class=\"alignnone size-full wp-image-3285\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-03.png\" alt=\"Transpose Using Power Query\" width=\"238\" height=\"238\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-03.png 238w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-03-150x150.png 150w\" sizes=\"(max-width: 238px) 100vw, 238px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 4:<\/span> <\/strong>Within here you need to select <strong><em>Transform &gt; Transpose<\/em><\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Transpose Using Power Query\"  class=\"alignnone size-full wp-image-3286\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-04.png\" alt=\"Transpose Using Power Query\" width=\"291\" height=\"123\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 5:<\/span> <\/strong>Now you will see your changes take place.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-05.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Transpose Using Power Query\"  class=\"alignnone size-full wp-image-3287\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-05.png\" alt=\"Transpose Using Power Query\" width=\"1063\" height=\"82\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-05.png 1063w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-05-300x23.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-05-1024x79.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-05-768x59.png 768w\" sizes=\"(max-width: 1063px) 100vw, 1063px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 6: <\/strong><\/span>Click <strong>Close &amp; Load<\/strong> from the <strong>Home<\/strong> tab and this will <strong>open up a brand new worksheet<\/strong> in your Excel workbook with the transposed data.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-06.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Transpose Using Power Query\"  class=\"alignnone size-full wp-image-3288\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-06.png\" alt=\"Transpose Using Power Query\" width=\"135\" height=\"129\" \/><\/a><\/p>\n<p>You now have your new transposed table.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-07.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Transpose Using Power Query\"  class=\"alignnone size-full wp-image-3289\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-07.png\" alt=\"Transpose Using Power Query\" width=\"993\" height=\"68\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-07.png 993w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-07-300x21.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Transpose-07-768x53.png 768w\" sizes=\"(max-width: 993px) 100vw, 993px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"text-decoration: underline; color: #0000ff;\">HELPFUL RESOURCE:\u00a0<\/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=\"3 Quick Ways on How To Create A List In Excel\"  class=\"alignnone wp-image-3233 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Oz_Podcast_Banner.jpg\" alt=\"3 Quick Ways on How To Create A List In Excel\" width=\"728\" height=\"90\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Oz_Podcast_Banner.jpg 728w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Oz_Podcast_Banner-300x37.jpg 300w\" sizes=\"(max-width: 728px) 100vw, 728px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Power Query lets you perform a series of steps to transform your Excel data. \u00a0One of the steps it allows you to take is to\u00a0transpose data very easily. Transposing\u00a0a data table is basically\u00a0rotating your data from rows to columns,\u00a0or from columns to rows. To further explain this concept, screenshots are provided below. \ufeff Download excel [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17266,"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,130],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3278"}],"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=3278"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3278\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17266"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=3278"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=3278"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=3278"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}