{"id":5169,"date":"2019-04-22T10:41:21","date_gmt":"2019-04-22T15:41:21","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=5169"},"modified":"2019-04-22T10:43:19","modified_gmt":"2019-04-22T15:43:19","slug":"did-you-know-power-query-can-extract-a-unique-list-of-records","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=5169","title":{"rendered":"Did you know \u2026 Power Query can extract a unique list of records?"},"content":{"rendered":"<p>Remove duplicates is a quick way to obtain a unique list of records; every time the source data is updated, though, you\u2019ve got to copy and \u2018remove duplicates\u2019 again. There\u2019s a better way! Use Power Query to create a unique list that can be updated with a single click.<\/p>\n<p>To use Power Query, first highlight the column containing the information for which you want a list of unique values.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1490\" height=\"344\" class=\"wp-image-5170\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image-84.png\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-84.png 1490w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-84-300x69.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-84-768x177.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-84-1024x236.png 1024w\" sizes=\"auto, (max-width: 1490px) 100vw, 1490px\" \/><\/p>\n<p>On the \u201cData\u201d ribbon bar, select \u201cFrom Table\/Range\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"968\" height=\"254\" class=\"wp-image-5171\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image-85.png\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-85.png 968w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-85-300x79.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-85-768x202.png 768w\" sizes=\"auto, (max-width: 968px) 100vw, 968px\" \/><\/p>\n<p>You\u2019ll be asked to confirm where the source data is located \u2013 the highlighted selection should appear. Click \u201cOK\u201d to continue.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"344\" height=\"185\" class=\"wp-image-5172\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image-86.png\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-86.png 344w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-86-300x161.png 300w\" sizes=\"auto, (max-width: 344px) 100vw, 344px\" \/><\/p>\n<p>A new window will open \u2013 the Power Query Editor. On the \u201cHome\u201d ribbon bar, click on \u201cRemove Rows\u201d and select \u201cRemove Duplicates\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"562\" height=\"319\" class=\"wp-image-5173\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image-87.png\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-87.png 562w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-87-300x170.png 300w\" sizes=\"auto, (max-width: 562px) 100vw, 562px\" \/><\/p>\n<p>A unique list of values has been extracted in the Power Query editor \u2013 <em>but<\/em> you want to insert that data into your spreadsheet. Click the drop-down by \u201cClose &amp; Load\u201d then select \u201cClose &amp; Load To \u2026\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"412\" height=\"239\" class=\"wp-image-5174\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image-88.png\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-88.png 412w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-88-300x174.png 300w\" sizes=\"auto, (max-width: 412px) 100vw, 412px\" \/><\/p>\n<p>Now you can select where you want your list of unique values to appear \u2013 I am creating a table in an existing worksheet. Click \u201cOK\u201d to insert the unique list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"974\" height=\"453\" class=\"wp-image-5175\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image-89.png\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-89.png 974w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-89-300x140.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-89-768x357.png 768w\" sizes=\"auto, (max-width: 974px) 100vw, 974px\" \/><\/p>\n<p>Voila, I now have a unique list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"593\" height=\"376\" class=\"wp-image-5176\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image-90.png\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-90.png 593w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-90-300x190.png 300w\" sizes=\"auto, (max-width: 593px) 100vw, 593px\" \/><\/p>\n<p>What happens when new records are added to my source data? The Power Query table does <em>not<\/em> automatically update as values are added to the source data. On the \u201cData\u201d ribbon bar, click \u201cRefresh All\u201d to update the unique value list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"668\" height=\"202\" class=\"wp-image-5177\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2019\/04\/word-image-91.png\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-91.png 668w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2019\/04\/word-image-91-300x91.png 300w\" sizes=\"auto, (max-width: 668px) 100vw, 668px\" \/><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Remove duplicates is a quick way to obtain a unique list of records; every time the source data is updated, though, you\u2019ve got to copy and \u2018remove duplicates\u2019 again. There\u2019s a better way! Use Power Query to create a unique list that can be updated with a single click. To use Power Query, first highlight &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[675,708,709,691,729,731],"class_list":["post-5169","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-did-you-know","tag-excel","tag-microsoft-excel","tag-microsoft-office","tag-microsoft-office-365","tag-ms-excel"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5169","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5169"}],"version-history":[{"count":2,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5169\/revisions"}],"predecessor-version":[{"id":5179,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5169\/revisions\/5179"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5169"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5169"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5169"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}