{"id":3307,"date":"2016-07-06T01:52:25","date_gmt":"2016-07-05T23:52:25","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=3307"},"modified":"2024-02-06T16:17:49","modified_gmt":"2024-02-06T15:17:49","slug":"group-rows-get-counts-using-power-query","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/group-rows-get-counts-using-power-query\/","title":{"rendered":"Group Rows and Get Counts 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.<\/p>\n<p>One of the steps it allows you to take is to\u00a0<strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/how-to-group-rows-in-excel\/\" target=\"_blank\" rel=\"noopener\">group rows<\/a> and get the counts of each group<\/strong><strong>\u00a0very easily<\/strong>.<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/TLXCNjUtqII?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"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\/Group-By-Count.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Group-By-Count.xlsx<\/span><\/a><\/p>\n<p>Let\u2019s go through the steps in detail:<\/p>\n<p>&nbsp;<\/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\/Group-By-Count-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Group Rows and Get Counts Using Power Query\" class=\"alignnone size-full wp-image-3310\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-01.png\" alt=\"Group Rows and Get Counts Using Power Query\" width=\"630\" height=\"386\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-01.png 630w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-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\/Group-By-Count-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Group Rows and Get Counts Using Power Query\" class=\"alignnone size-full wp-image-3311\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-02.png\" alt=\"Group Rows and Get Counts Using Power Query\" width=\"618\" height=\"132\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-02.png 618w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-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 group this data by\u00a0<strong>Country<\/strong> and show\u00a0how many times each Country appeared in the table. (i.e. Australia appears 4 times in this table)<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Group Rows and Get Counts Using Power Query\" class=\"alignnone size-full wp-image-3312\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-03.png\" alt=\"Group Rows and Get Counts Using Power Query\" width=\"238\" height=\"238\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-03.png 238w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-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;\u00a0Group By<\/em><\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-04.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Group Rows and Get Counts Using Power Query\" class=\"alignnone size-full wp-image-3313\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-04.png\" alt=\"Group Rows and Get Counts Using Power Query\" width=\"200\" height=\"112\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000\">STEP 5:<\/span>\u00a0<\/strong>Make sure to select\u00a0<strong>Country\u00a0<\/strong>for\u00a0<strong>Group By,\u00a0<\/strong>and select\u00a0<strong>Count Rows\u00a0<\/strong>for the\u00a0<strong>Operation.<\/strong><\/p>\n<p>This will group your table by\u00a0<strong>Country\u00a0<\/strong>value, and count the number of occurrences of each\u00a0country.<\/p>\n<p>For example, the country of\u00a0<strong>Australia\u00a0<\/strong>appears\u00a0<strong>4 times<\/strong> in our table.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-05.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Group Rows and Get Counts Using Power Query\" class=\"alignnone size-full wp-image-3314\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-05.png\" alt=\"Group Rows and Get Counts Using Power Query\" width=\"699\" height=\"307\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-05.png 699w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-05-300x132.png 300w\" sizes=\"(max-width: 699px) 100vw, 699px\" \/><\/a><\/p>\n<p><strong style=\"line-height: 1.5\"><span style=\"color: #ff0000\">STEP 6:<\/span> <\/strong><span style=\"line-height: 1.5\">Now you will see your changes take place. And the data has now been\u00a0<strong>grouped together.<\/strong><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-06.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Group Rows and Get Counts Using Power Query\" class=\"alignnone size-full wp-image-3315\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-06.png\" alt=\"Group Rows and Get Counts Using Power Query\" width=\"206\" height=\"93\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 7: <\/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 new\u00a0data.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-07.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Group Rows and Get Counts Using Power Query\" class=\"alignnone size-full wp-image-3316\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-07.png\" alt=\"Group Rows and Get Counts Using Power Query\" width=\"135\" height=\"129\" \/><\/a><\/p>\n<p>You now have your new table with the counts of each country.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-08.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Group Rows and Get Counts Using Power Query\" class=\"alignnone size-full wp-image-3317\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/06\/Group-By-Count-08.png\" alt=\"Group Rows and Get Counts Using Power Query\" width=\"174\" height=\"107\" \/><\/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 steps it allows you to take is to\u00a0group rows and get the counts of each group\u00a0very easily. \ufeff Download excel workbookGroup-By-Count.xlsx Let\u2019s go through the steps in detail: &nbsp; STEP 1: Select\u00a0your data and turn it into an [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17277,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Group Rows and Get Counts Using Power Query","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[120,121],"tags":[133,132,117],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3307"}],"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=3307"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3307\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17277"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=3307"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=3307"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=3307"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}